CODEWITHSUNDEEP
sql-server-2016
Aiyoub A.
Aiyoub A.

Reputation: 5391

How can I create a temporal table by specifying file groups?

I create a system versioning table for history operation, but I want to specify a separate file group such as FG_History for temporal table.

How do I change the following query:

CREATE TABLE [dbo].[ExpenseCenter]
(
    [ExpenseCenterId] [tinyint] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](200) NOT NULL,
    [SysStartTime] datetime2 (2) GENERATED ALWAYS AS ROW START,
    [SysEndTime] datetime2 (2) GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime), 

    CONSTRAINT [PK_ExpenseCenter] 
        PRIMARY KEY CLUSTERED ([ExpenseCenterId] ASC)
                    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                          IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                          ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [FG_INDEX],
    CONSTRAINT [UK_ExpenseCenterName] 
        UNIQUE NONCLUSTERED ([Name] ASC)
                    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                          IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                          ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [FG_INDEX]
) ON [FG_DATA]
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = history.ExpenseCenterHistory))
GO

I will be grateful if someone could guide me.

Upvotes: 4

Views: 650

Answers (1)

dfundako
dfundako

Reputation: 8314

If I understand, you want the primary table to be on one file group and the archive on another file group. You can try scripting out the 'Drop and Create to' option through the object explorer for the history table and specify which filegroup you want the clustered index to be in.

Upvotes: 5

Related Questions