Temporal Tables

dbo.ItemHistorical (0 Rows)

We have data in the main table, but thus far no changes have been made so nothing is in the historical table.  Now let's make some changes:

-- Update Price on Optical Mouse

UPDATE dbo.Item SET MSRP = 27.95 WHERE ItemId = 3;

-- Change description of Mouse Pad

UPDATE dbo.Item SET Description = 'Useful Mouse Pad' WHERE ItemId = 1;

Now let's check the tables:

dbo.Item (still 3 rows with our changes)

dbo.ItemHistorical (2 new rows)

Let's see what happened ...

As our data changed and we select from our main [dbo].[Item] table it always shows the most recent data, plus our historical data is now stored in [dbo].[ItemHistorical].  To look a bit more under the hood we can run this to view all data in [dbo].[Item] which shows all records using the ValidFrom and ValidTo which is a great way to view the full history of the data:

SELECT    ItemID, Name, QuantityOnHand, Description, MSRP, ValidFrom, ValidTo   

FROM    [dbo].[Item]

FOR SYSTEM_TIME ALL

What are we looking at??  When querying Temporal Tables we now have a new option called "FOR SYSTEM_TIME" which allows querying the data based on various methods using ValidFrom and ValidTo, or as this above you can view ALL items.  This is often used for reporting if someone needs to view all changes to or from a point or at a very specific point.  We can even run this to see what the data was when the items were created:

SELECT    ItemID,

       Name,

       QuantityOnHand,

       Description,

       MSRP,

       ValidFrom,

       ValidTo   

FROM    [dbo].[Item]

FOR SYSTEM_TIME AS OF '2023-12-19 20:53:39.5770378'

This shows a few things, we can view all the original data based on the creation date, plus we can see that two of these items are no longer valid.  Below are some of these options allowed to use (From Microsoft Support): 

• AS OF <date_time>

• FROM <start_date_time> TO <end_date_time>

• BETWEEN <start_date_time> AND <end_date_time>

• CONTAINED IN (<start_date_time>, <end_date_time>)

• ALL

From this you can see the power of how this can be used. Now be mindful, this tracks every change to the table, so if a process updates multiple fields one at a time or creates constant updates this log will become very long.  

Work With History

This all sounds great, but what happens 1 year, 2 years, 5 years down the road now that you have this mammoth of a history table.  What are some means to keep the History table from growing perpetually?  Microsoft has a great article about this below, but I'll highlight some of these items below as well:

Microsoft: Manage retention of historical data in system-versioned temporal tables:
https://learn.microsoft.com/en-us/sql/relational-databases/tables/manage-retention-of-historical-data-in-system-versioned-temporal-tables

Option 1 - Log Retention

Preset the logs to drop off after a specific period of time.  For example, if we only need to keep 9 months of logs we can add this to the table definition either on Create or Alter.  Below is an example of this as an Alter:

ALTER TABLE dbo.Item

SET (SYSTEM_VERSIONING = ON (HISTORY_RETENTION_PERIOD = 9 MONTHS));

With this after 9 months the items in the Historical table will start dropping off.

Option 2 - Rolling Logs through Table Partitions with a Sliding Window

To show an example of this is well beyond what can be put into a small article, but the link above form Microsoft goes through this scenario.  What it does is create Table Partitions which can be used to write logs to, and the Partitioned Table used for the active log is updated periodically allowing the logs to rotate the partitioned tables.  This would not only allow the active table to remain small, and older historical data could be pruned or copied to another location as needed to keep the database size in good shape.  

Option 3 - Manually Delete or Alter Data

To make any changes to the Historical table SYTEM_VERSIONING would need to be disabled, but to avoid missing any changes this must all be handled within a single Transation.  Below is an example:

BEGIN TRANSACTION

   ALTER TABLE [dbo].[Item] SET ( SYSTEM_VERSIONING = OFF )

   SET @sql = 'DELETE FROM [dbo].[Item] WITH (TABLOCKX) WHERE [ItemId] = ''' + CAST(@Id AS VARCHAR(40)) + ''''

   EXECUTE sp_executesql (@sql)

   ALTER TABLE [dbo].[Item] SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[ItemHistorical], DATA_CONSISTENCY_CHECK = OFF))

COMMIT TRANSACTION

When this runs it will disable SYTEM_VERSIONING, delete the data, then re-enable all within the same transaction.  If this runs longer than just a few moments and changes to dbo.Item come in it'll be retained within the Log and applied after the Transaction is Committed. This will avoid any loss of data.

Removing Temporal Feature from a Table

If you decide Temporal Tables just aren't working for you, it's quite simple to disable it on a table:

ALTER TABLE dbo.Item SET ( SYSTEM_VERSIONING = OFF)

This will disable SYSTEM_VERSIONING on the [dbo].[Item] table, but all the items setup like [dbo].[ItemHistorical] will still be there along with the ValidFrom and ValidTo fields.  These can now be safely dropped as you see fit. Here's the full process:

ALTER TABLE dbo.Item SET ( SYSTEM_VERSIONING = OFF);

GO

ALTER TABLE dbo.Item DROP PERIOD FOR SYSTEM_TIME;

ALTER TABLE dbo.Item DROP COLUMN ValidFrom, ValidTo;

DROP TABLE dbo.ItemHistorical;

GO

After this selecting all data from [dbo].[Item] will return the latest data from the Item table:

SELECT    ItemID,Name,QuantityOnHand,Description,MSRP,ValidFrom,ValidTo

FROM    [dbo].[item]