Temporal Tables in SQL Server 2016 - More Control

In our pervious post, when letting the system create temporal tables, it will create the table in the default filegroup that the table is in. But what if you want to manage the object placement of the temporal table, such as indexes. This demo will educate you on how to create the teproal table and allow you to manage the placement of indexes associated with the temporal table.

Step 1 - Create the file group and file

We will create a database called SonaneDW and create a file group for indexes. The [FG_IDX] will host the index for our temporal table. 
CREATE DATABASE [SONANEDW]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'SONANEDW', 
	FILENAME = N'G:\MSSQLSERVER\SONANEDW.MDF' 
	, SIZE = 8192KB 
	, MAXSIZE = UNLIMITED
	, FILEGROWTH = 65536KB ), 
 FILEGROUP [FG_IDX] 
( NAME = N'SONANEDW_IDX', 
	FILENAME = N'G:\MSSQLSERVER\SONANEDW_IDX.NDF' 
	, SIZE = 8192KB 
	, MAXSIZE = UNLIMITED
	, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'SONANEDW_LOG', 
	FILENAME = N'F:\MSSQLSERVER\SONANEDW_LOG.LDF' 
	, SIZE = 8192KB 
	, MAXSIZE = 2048GB 
	, FILEGROWTH = 65536KB )
GO
					


					

					


Step 2 - Create the History table and indexes

We will create a database called SonaneDW and create a file group for indexes.  The [FG_IDX] will host the index for our temporal table.





					
CREATE TABLE HUMANRESOURCES.BENEFITSHISTORY
 (  
   ENTITYID INT NOT NULL,
   PROVIDERNAME NVARCHAR(100) NOT NULL, 
   EMPLOYEEID INT NOT NULL,
   MEDICALELECTION VARCHAR(100) NOT NULL,
   DENTALELECTION VARCHAR(100) NOT NULL,
   VISIONELECTION VARCHAR(100) NOT NULL,
   ELECTIONYEAR VARCHAR(4) NOT NULL,
   STARTTIME DATETIME2 NOT NULL,
   ENDTIME DATETIME2 NOT NULL
)
GO
CREATE NONCLUSTERED INDEX [IDX_BENEFITS_PROVIDERNAME] ON [HUMANRESOURCES].[BENEFITSHISTORY]
(
    [PROVIDERNAME] ASC
)ON [FG_IDX]
GO
					


Step 3 - Create Temporal Table

In this last step, we will create the table that data will be inserted/updated/deleted and map this to the BenefitsHistory table.
SQL Server will, by default, perform a consistency check when you create the table or convert a table.  The following conditions must be True in order to create the table

    

  1. The current and history tables have the same number of columns.
    2. 

  2. The corresponding columns in each table have matching data types.
    3. 

  3. The start- and end-period columns are nonnullable
    4. 

  4. A primary key constraint exists in the current table and does not exist in the history table.
    5. 

  5. If the current table has hidden period columns, the history table must also hide the period columns.
    6. 

  6. There is no identity column in the history table.
    7. 

  7. There are no triggers in the history table.
    8. 

  8. There are no foreign keys in the history table.
    9. 

  9. There are no table or column constraints (aside from default column values) in the history table.
    10. 

  10. The history table cannot be in a read-only filegroup.
    11. 

  11. Change tracking or change data capture cannot be enabled for the history table.
    12. 







					
CREATE TABLE HUMANRESOURCES.BENEFITS 
 (  
   ENTITYID INT NOT NULL,
    CONSTRAINT [PK_ENTITY_ID] PRIMARY KEY CLUSTERED  (ENTITYID) ON [FG_IDX],
   PROVIDERNAME NVARCHAR(100) NOT NULL, 
   EMPLOYEEID INT NOT NULL,
   MEDICALELECTION VARCHAR(100) NOT NULL,
   DENTALELECTION VARCHAR(100) NOT NULL,
   VISIONELECTION VARCHAR(100) NOT NULL,
   ELECTIONYEAR VARCHAR(4) NOT NULL,
   STARTTIME DATETIME2 GENERATED ALWAYS AS ROW START
             NOT NULL,
   ENDTIME   DATETIME2 GENERATED ALWAYS AS ROW END
             NOT NULL,
   PERIOD FOR SYSTEM_TIME (STARTTIME, ENDTIME)   
 ) 
 WITH(SYSTEM_VERSIONING= ON (HISTORY_TABLE=HUMANRESOURCES.BENEFITSHISTORY))
GO
					


Step 4 - View the objects in the File Group

The SELECT statement below will display the objects and which filegroup they belong too.





					
SELECT 
    OJ.[NAME]
    , OJ.[TYPE]
    , IDX.[NAME]
    , IDX.[INDEX_ID]
    , FG.[NAME] 
FROM SYS.INDEXES IDX
INNER JOIN SYS.FILEGROUPS FG
ON 
    IDX.DATA_SPACE_ID = FG.DATA_SPACE_ID
INNER JOIN SYS.ALL_OBJECTS OJ
ON 
    IDX.[OBJECT_ID] = OJ.[OBJECT_ID] 
WHERE 
    IDX.DATA_SPACE_ID = FG.DATA_SPACE_ID
AND 
    OJ.TYPE = 'U'
GO