SQL Server 2017 Temporal Enhancements

One of the most popular features in my talks about SQL Server 2016 has been the temporal tables feature. If you aren’t familiar with this feature you can read more about it on Books Online here. In a nutshell, you get a second table that tracks the lineage of your data. This is fantastic for all sorts of scenarios up to and including auditing, data recovery, fraud detection, or even slowly changing dimensions.

th

This is implemented in SQL Server via a history table—a second copy of your data that maintains timestamps of when the data is valid. As you can imagine this table can grow quite large—Microsoft does us a couple of favors: the history table is page compressed by default (you can use columnstore) and you could put the history table on a different filegroup. The only major issue was to truncate or delete data from history table for pruning purposes, you had to turn of system versioning, or the glue that makes that this feature work.

Starting with SQL Server 2017 (and Azure SQL Database) you can define a retention period and have SQL Server prune records for you. This is awesome and easy—see how to implement here.

SQL Server 2017—SELECT INTO With Filegroup Support

One of the things I really appreciate Microsoft doing in recent releases of SQL Server is fixing some of the longstanding Connect items. These aren’t necessarily bugs, but design gaps—a good example of this was with SQL Server 2016, where the ability to truncate an individual partition came into effect. Some of these are minor, but have real impact into usability and functionality of the RDBMS.

Image result for file cabinet

The feature I am highlighting here is the SELECT INTO syntax for SQL Server. In the past, this syntax could only be used to create the new table in the user’s default filegroup. There were workarounds like changing the default filegroup for the user, but it was an extra step that shouldn’t have been needed. Starting with SQL Server 2017, T-SQL will support the ON syntax in this command:

SELECT * INTO dbo.NewProducts FROM Production.Product ON SecondaryFG

This isn’t huge, but it is a very nice thing that Microsoft has fixed.

%d bloggers like this: