In Temporal Tables in SQL Server 2016 – Part 1 we looked at the basic setup and querying of temporal tables in SQL Server 2016.
In this part we’ll look at some of the other standard SQL Server options we can use in the database engine together with temporal tables to provide further flexibility.
Showing the execution plan for the query covering all , including system-versioned, rows shows us that it queries both tables and concatenates the results, and this is to be expected:
We can also change the compression, in enterprise edition, to Page level so as to reduce the storage required and place the CompanyHistory table on a different file within a filegroup, such as slow traditional spindle storage.
Firstly we’ll create the filegroup and file:
ALTER DATABASE [Test] ADD FILEGROUP [SLOWSTOREFILEGROUP];
ALTER DATABASE [Test] ADD FILE ( NAME = N’Test_File’, FILENAME = N’F:DATATest_File.ndf’)
TO FILEGROUP [SLOWSTOREFILEGROUP]
Then we can move the current clustered index of our CompanyHistory history, ix_CompanyHistory to the new filegroup and, optionally in enterprise edition, turn on Page compression.
CREATE CLUSTERED INDEX [ix_CompanyHistory] ON [dbo].[CompanyHistory]
(
[SysEndTime] ASC,
[SysStartTime] ASC
) WITH (DROP_EXISTING = ON, DATA_COMPRESSION = PAGE) ON [SLOWSTOREFILEGROUP]
And we can see it has applied:
