AcumaticaCustomSQLTableExample/FiledHistory.SQL

USE [master];
GO

-- STEP 0: Add Filegroups to AcumaticaDB
ALTER DATABASE [AcumaticaDB] ADD FILEGROUP FG_WFHT_0;
ALTER DATABASE [AcumaticaDB] ADD FILEGROUP FG_WFHT_1;
ALTER DATABASE [AcumaticaDB] ADD FILEGROUP FG_WFHT_2;
ALTER DATABASE [AcumaticaDB] ADD FILEGROUP FG_WFHT_3;
ALTER DATABASE [AcumaticaDB] ADD FILEGROUP FG_WFHT_4;
ALTER DATABASE [AcumaticaDB] ADD FILEGROUP FG_WFHT_5;
ALTER DATABASE [AcumaticaDB] ADD FILEGROUP FG_WFHT_6;
ALTER DATABASE [AcumaticaDB] ADD FILEGROUP FG_WFHT_7;
ALTER DATABASE [AcumaticaDB] ADD FILEGROUP FG_WFHT_8;
ALTER DATABASE [AcumaticaDB] ADD FILEGROUP FG_WFHT_9;
GO

-- STEP 0.1: Add Files to Filegroups (adjust paths if needed)
ALTER DATABASE [AcumaticaDB] ADD FILE (NAME = WFHT_0, FILENAME = 'C:\SQLData\WFHT_0.ndf') TO FILEGROUP FG_WFHT_0;
ALTER DATABASE [AcumaticaDB] ADD FILE (NAME = WFHT_1, FILENAME = 'C:\SQLData\WFHT_1.ndf') TO FILEGROUP FG_WFHT_1;
ALTER DATABASE [AcumaticaDB] ADD FILE (NAME = WFHT_2, FILENAME = 'C:\SQLData\WFHT_2.ndf') TO FILEGROUP FG_WFHT_2;
ALTER DATABASE [AcumaticaDB] ADD FILE (NAME = WFHT_3, FILENAME = 'C:\SQLData\WFHT_3.ndf') TO FILEGROUP FG_WFHT_3;
ALTER DATABASE [AcumaticaDB] ADD FILE (NAME = WFHT_4, FILENAME = 'C:\SQLData\WFHT_4.ndf') TO FILEGROUP FG_WFHT_4;
ALTER DATABASE [AcumaticaDB] ADD FILE (NAME = WFHT_5, FILENAME = 'C:\SQLData\WFHT_5.ndf') TO FILEGROUP FG_WFHT_5;
ALTER DATABASE [AcumaticaDB] ADD FILE (NAME = WFHT_6, FILENAME = 'C:\SQLData\WFHT_6.ndf') TO FILEGROUP FG_WFHT_6;
ALTER DATABASE [AcumaticaDB] ADD FILE (NAME = WFHT_7, FILENAME = 'C:\SQLData\WFHT_7.ndf') TO FILEGROUP FG_WFHT_7;
ALTER DATABASE [AcumaticaDB] ADD FILE (NAME = WFHT_8, FILENAME = 'C:\SQLData\WFHT_8.ndf') TO FILEGROUP FG_WFHT_8;
ALTER DATABASE [AcumaticaDB] ADD FILE (NAME = WFHT_9, FILENAME = 'C:\SQLData\WFHT_9.ndf') TO FILEGROUP FG_WFHT_9;
GO

-- STEP 1: Drop existing table if it exists
USE [AcumaticaDB];
GO
IF OBJECT_ID('dbo.FieldHistoryTracker', 'U') IS NOT NULL
    DROP TABLE dbo.FieldHistoryTracker;
GO

-- STEP 2: Create Partition Function
IF NOT EXISTS (SELECT * FROM sys.partition_functions WHERE name = 'pf_FieldHistoryTracker')
    CREATE PARTITION FUNCTION pf_FieldHistoryTracker (INT)
    AS RANGE LEFT FOR VALUES (1,2,3,4,5,6,7,8,9);
GO

-- STEP 3: Create Partition Scheme
IF NOT EXISTS (SELECT * FROM sys.partition_schemes WHERE name = 'ps_FieldHistoryTracker')
    CREATE PARTITION SCHEME ps_FieldHistoryTracker
    AS PARTITION pf_FieldHistoryTracker TO 
    (FG_WFHT_0, FG_WFHT_1, FG_WFHT_2, FG_WFHT_3, FG_WFHT_4,
     FG_WFHT_5, FG_WFHT_6, FG_WFHT_7, FG_WFHT_8, FG_WFHT_9);
GO

-- STEP 4: Create Table using partition scheme
CREATE TABLE dbo.FieldHistoryTracker (
    ID INT IDENTITY(1,1) NOT NULL,
    CompanyID INT NOT NULL,
    FieldCode NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    FieldCodeHash AS (ABS(CHECKSUM(FieldCode)) % 10) PERSISTED,
    KeyType NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    RelatedKey NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    UDF BIT DEFAULT 0 NOT NULL,
    Active BIT DEFAULT 0 NOT NULL,
    FieldDescription NVARCHAR(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    FieldValueCode NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    FieldValueText NVARCHAR(200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    FieldValueDecimal DECIMAL(18,6) NULL,
    AppliedDate DATETIME NOT NULL,
    AppliedbyUserID UNIQUEIDENTIFIER NULL,
    AppliedbyUserName NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    LastModifiedDateTime DATETIME NOT NULL,
    LastModifiedByName NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    LastModifiedByID UNIQUEIDENTIFIER NULL,
    PRIMARY KEY CLUSTERED (FieldCodeHash, ID)
        ON ps_FieldHistoryTracker(FieldCodeHash),
    UNIQUE (FieldCodeHash, FieldCode, CompanyID, ID)
);
GO

-- STEP 5: Create supporting indexes
CREATE INDEX IX_FieldHistoryTracker_FieldCodeHash
ON dbo.FieldHistoryTracker (FieldCodeHash, FieldCode)
ON ps_FieldHistoryTracker(FieldCodeHash);
GO

CREATE INDEX IX_FieldHistoryTracker_Active_UDF 
ON dbo.FieldHistoryTracker (CompanyID, Active, UDF, AppliedDate);
GO

CREATE INDEX IX_FieldHistoryTracker_FieldValue 
ON dbo.FieldHistoryTracker (FieldValueText, AppliedDate, AppliedbyUserID, AppliedbyUserName, Active, UDF);
GO

CREATE INDEX IX_FieldHistoryTracker_AppliedDate 
ON dbo.FieldHistoryTracker (AppliedDate);
GO

CREATE INDEX IX_FieldHistoryTracker_AppliedByUser 
ON dbo.FieldHistoryTracker (AppliedbyUserID, AppliedbyUserName);
GO

CREATE INDEX IX_FieldHistoryTracker_AppliedByUser_Active 
ON dbo.FieldHistoryTracker (AppliedbyUserID, Active, AppliedDate);
GO

CREATE INDEX IX_FieldHistoryTracker_KeyType_RelatedKey 
ON dbo.FieldHistoryTracker (KeyType, RelatedKey);
GO

CREATE INDEX IX_FieldHistoryTracker_FieldCodeValueType 
ON dbo.FieldHistoryTracker (FieldCode, FieldValueCode);
GO