Creating a System-Versioned Temporal Table<\/h2>\n
Table versioning can be created on entirely new tables, or on existing tables. The history table, or the table where changes are logged, can be:<\/p>\n
- \n
- An entirely new, \u2018anonymous\u2019 table with no name specified in which case SQL Server creates a table and assigns a name,<\/li>\n
- A \u2018default\u2019 table with a name as desired by you,<\/li>\n
- An existing table with data that you now want to use as a history log.<\/li>\n<\/ul>\n
To get started, create an entirely new table and version it first.<\/p>\n
CREATE TABLE [dbo].[Region]\r\n(RegionID INT IDENTITY(1,1) NOT NULL \r\n CONSTRAINT PK_Region PRIMARY KEY CLUSTERED,\r\n RegionDescription VARCHAR(100) NULL,\r\n StartDateTime datetime2 generated always as row start NOT NULL,\r\n EndDateTime datetime2 generated always as row end NOT NULL,\r\n PERIOD FOR SYSTEM_TIME (StartDateTime, EndDateTime))\r\n WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Region_History));\r\n\r\n INSERT INTO [dbo].[Region]\r\n (RegionDescription)\r\n VALUES ('North USA')\r\nINSERT INTO [dbo].[Region]\r\n (RegionDescription)\r\n VALUES\r\n ('South USA'),\r\n ('NorthEast USA')\r\nSELECT * FROM [dbo].[Region]<\/pre>\n![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2019\/03\/word-image-41.png\")
<\/p>\nIt is easy to see that the column
StartDateTime<\/code> is populated with current date and time in UTC, andEndDateTime<\/code> is the max value that can be specified for a datetime2 data type. These are not specified in the insert statements and do not have defaults defined, they get autopopulated. Notice the syntax in theCREATE<\/code>TABLE<\/code> statement, generated always as row start and generated always as row end. <\/p>\nNow take a look at what was logged in history table:<\/p>\n
SELECT * FROM [dbo].[Region_History]<\/pre>\n
This returns nothing. This is because logging is limited to updates and deletes and does not log inserts.<\/p>\n
Now, if you run an update and then look at the history table, you will see that the previous row has been logged. The
StartDateTime<\/code> andEndDateTime<\/code> values specify exactly when this row was active.<\/p>\nUPDATE [dbo].[Region] SET RegionDescription = 'NorthEast US' \r\nWHERE RegionDescription = 'NorthEast USA'\r\nSELECT * FROM [dbo].[Region_History]<\/pre>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2019\/03\/word-image-42.png\")
<\/p>\nIf you look at the main table for the same row, you can see that it has a new start date that matches the date when previous version of the row was retired.<\/p>\n
SELECT * FROM [dbo].[Region]<\/pre>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2019\/03\/word-image-43.png\")
<\/p>\nDeleting a row also works similarly. When the end date of the deleted row matches date when it was deleted and there is no matching row in the main table.<\/p>\n
DELETE FROM [dbo].[Region] WHERE RegionID = 3\r\nSELECT * FROM [dbo].[Region_History] WHERE RegionID = 3<\/pre>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2019\/03\/word-image-44.png\")
<\/strong><\/p>\nAdding Versioning an Existing Table<\/h2>\n
The next scenario is to transition an existing history table to versioning. Trigger-based change tracking is still a very common and easy-to-implement process used at many places. This example explores a simple way this was implemented and how to use the same table, without changing or deleting any data to implement versioning.<\/p>\n
IF (EXISTS (SELECT * \r\n FROM INFORMATION_SCHEMA.TABLES \r\n WHERE TABLE_SCHEMA = 'dbo' \r\n AND TABLE_NAME = 'Region'))\r\n\tBEGIN\r\n\tALTER TABLE dbo.Region SET (system_versioning = off)\r\n\tDROP TABLE dbo.Region\r\n\tDROP TABLE dbo.Region_History\r\n\tEND\r\ngo\r\nCREATE TABLE [dbo].[Region](\r\n\t[RegionID] [int] IDENTITY(1,1) NOT NULL,\r\n\t[RegionDescription] [varchar](100) NULL,\r\n\t[CreateUser] [nvarchar](100) NOT NULL default \r\n (COALESCE(SUSER_NAME(SUSER_ID()),USER_NAME())),\r\n\t[CreateDate] DateTime NOT NULL default getdate(),\r\n\t[UpdateUser] [nvarchar](100) NOT NULL default \r\n (COALESCE(SUSER_NAME(SUSER_ID()),USER_NAME())),\r\n\t[UpdateDate] DateTime NOT NULL default getdate()\r\n CONSTRAINT [PK_Region] PRIMARY KEY CLUSTERED \r\n(\r\n\t[RegionID] ASC\r\n)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, \r\n IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, \r\n ALLOW_PAGE_LOCKS = ON) ) \r\nCREATE TABLE [dbo].[Region_History](\r\n\t[RegionHistoryID] [int] IDENTITY(1,1) NOT NULL,\r\n\t[RegionID] [int] NOT NULL,\r\n\t[RegionDescription] [varchar](100) NULL,\r\n\t[CreateUser] [nvarchar](100) NOT NULL ,\r\n\t[CreateDate] DateTime NOT NULL ,\r\n\t[UpdateUser] [nvarchar](100) NOT NULL \r\n default (COALESCE(SUSER_NAME(SUSER_ID()),USER_NAME())),\r\n\t[UpdateDate] DateTime NOT NULL default getdate()\r\n) \r\nGO<\/pre>\n
There are two simple triggers, one for updates and one for deletes, to track changes to the table.<\/p>\n
CREATE TRIGGER [dbo].[Region_Update] on [dbo].[Region]\r\nAFTER UPDATE\r\nAS\r\nBEGIN\r\n INSERT INTO dbo.Region_History \r\n (RegionId,RegionDescription,CreateUser,CreateDate,\r\n UpdateUser,UpdateDate)\r\n SELECT i.RegionId,i.RegionDescription,i.CreateUser,i.CreateDate,\r\n SUSER_SNAME(), getdate()\r\n from dbo.Region r \r\n inner join inserted i on r.RegionID=i.RegionID \r\nEND\r\nGO\r\nCREATE TRIGGER [dbo].[Region_Delete] \r\nON [dbo].[Region] \r\nAFTER DELETE \r\nAS \r\n INSERT INTO [dbo].[Region_History]\r\n([RegionID],[RegionDescription],[CreateUser],\r\n [CreateDate],UpdateUser,UpdateDate )\r\nSELECT [RegionID],[RegionDescription],[CreateUser],[CreateDate],\r\n SUSER_SNAME(), getdate() FROM DELETED\r\nGO\r\n--Now insert data into the main table. \r\nINSERT INTO [dbo].[Region]\r\n(RegionDescription)\r\nvalues \r\n('Northeast')\r\n,('Southwest')\r\n,('West')\r\n,('Southeast')\r\n,('Midwest');\r\nSELECT * FROM [dbo].[Region]<\/pre>\n![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2019\/03\/word-image-45.png\")
<\/p>\nIntentionally change the same records several times so that the history table has a decent volume of data. This script will take about 10 minutes to run as you are recreating a history table with several updates with different timestamps on them.<\/p>\n
DECLARE @counter INT\r\nSELECT @COUNTER = 100\r\nWHILE @counter > 0\r\nBEGIN\r\nUPDATE [dbo].[Region]\r\nSET RegionDescription = 'NorthEast' \r\nWHERE RegionDescription = 'Northeast'\r\nWAITFOR DELAY '00:00:01'\r\nUPDATE [dbo].[Region]\r\nSET RegionDescription = 'Southwest ' \r\nWHERE RegionDescription = 'Southwest'\r\nWAITFOR DELAY '00:00:01'\r\nUPDATE [dbo].[Region]\r\nSET RegionDescription = 'Southeast ' \r\nWHERE RegionDescription = 'Southeast'\r\nWAITFOR DELAY '00:00:01'\r\nUPDATE [dbo].[Region]\r\nSET RegionDescription = 'Midwest ' \r\nWHERE RegionDescription = 'Midwest'\r\nWAITFOR DELAY '00:00:01'\r\nUPDATE [dbo].[Region]\r\nSET RegionDescription = 'MidWest' \r\nWHERE RegionDescription = 'Midwest '\r\nWAITFOR DELAY '00:00:01'\r\nUPDATE [dbo].[Region]\r\nSET RegionDescription = 'SouthWest' \r\nWHERE RegionDescription = 'Southwest '\r\nWAITFOR DELAY '00:00:01'\r\nUPDATE [dbo].[Region]\r\nSET RegionDescription = 'SouthEast' \r\nWHERE RegionDescription = 'Southeast '\r\nSELECT @counter = @counter - 1\r\nEND<\/pre>\n
Also, delete a couple of records from the main table.<\/p>\n
DELETE FROM [dbo].[Region] WHERE RegionDescription = 'West'\r\nDELETE FROM [dbo].[Region] WHERE RegionDescription = 'MidWest'\r\nSELECT * FROM dbo.Region<\/pre>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2019\/03\/word-image-46.png\")
<\/p>\nYou\u2019ll see 702 rows in the history table.<\/p>\n
SELECT * FROM dbo.Region_History <\/pre>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2019\/03\/word-image-47.png\")
<\/p>\nThe goal is to transition these two tables to temporal tables by keeping this data intact and allowing for traditional querying as well as querying using temporal table methodology.<\/p>\n
As a first step, add start and end dates to both tables:<\/p>\n
ALTER TABLE dbo.Region\r\nADD [StartDate] [datetime2] NOT NULL DEFAULT (getdate()),\r\n[EndDate] [datetime2] NOT NULL \r\n DEFAULT (convert(datetime2, '9999-12-31 23:59:59.9999999'))\r\nALTER TABLE dbo.Region_History\r\nADD [StartDate] [datetime2] NOT NULL DEFAULT (getdate()),\r\n[EndDate] [datetime2] NOT NULL \r\n DEFAULT (convert(datetime2, '9999-12-31 23:59:59.9999999'))<\/pre>\n
The structures of the history table and main table must be identical for turning versioning on. Since there is one column, regionhistoryid, in the history table that is not in the main table, you can either get rid of it in the history table or add it to the main table. Getting rid of it will mean the history table has no key per the older method. This is not ideal if you want to query older data with that method. Instead, add it to the main table. You won\u2019t be using it, just add it to ensure conformance for this purpose.<\/p>\n
ALTER TABLE [dbo].[Region] ADD RegionHistoryId int;<\/pre>\n
The next step is to add the period to connect two new fields in the main table and then attempt to enable versioning.<\/p>\n
ALTER TABLE dbo.Region \r\nADD PERIOD FOR SYSTEM_TIME ([StartDate], [EndDate]) \r\nALTER TABLE dbo.Region \r\n SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Region_History, \r\n DATA_CONSISTENCY_CHECK = ON)) <\/pre>\n
This returns an error as shown below:<\/p>\n
![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2019\/03\/Error1.png\")
<\/p>\nSQL Server will not allow identity columns in a history table. The identity property must be removed, but the data in this column is needed. To solve this, create another column, move data there, drop this column and rename the new column to the old name.<\/p>\n
ALTER TABLE [dbo].[Region_History] ADD RegionHistId int;\r\nGO\r\nUPDATE [dbo].[region_history] SET regionhistid = regionhistoryid;\r\nGO\r\nALTER TABLE [dbo].[region_history] DROP COLUMN regionhistoryid;\r\nGO\r\nEXEC sp_RENAME 'dbo.region_history.RegionHistid' \r\n , 'RegionHistoryID', 'COLUMN';\r\nGO<\/pre>\n
Now that the identity column is removed from the history table, try to turn versioning on again. This time you\u2019ll get another error.<\/p>\n
ALTER TABLE dbo.Region \r\n SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Region_History, \r\n DATA_CONSISTENCY_CHECK = ON))<\/pre>\n
![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2019\/03\/Error2.png\")
<\/p>\nThe data consistency check runs
DBCC<\/code>CHECKCONSTRAINT<\/code> under the hood and comes up with issues if constraints do not validate. The default value of the newEndDate<\/code> column is the maximum date of the system which, of course, is in the future.<\/p>\nThere are several ways to resolve this problem. One way is to enable versioning but to skip the checks. Don\u2019t run this code, but here it is for your reference:<\/p>\n
ALTER TABLE dbo.Region \r\n SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Region_History, \r\n DATA_CONSISTENCY_CHECK = OFF))<\/pre>\n
This essentially means the data that is currently in the history table cannot be queried on with methods used to query temporal tables. You may also run into issues with querying any new data because older data is bad and has time period overlaps. This method is not recommended since it carries a lot of risk.<\/p>\n
Instead, it is better to fix the data for time periods to match what is expected when using temporal table methodologies. Each history record must have a start and end date in the past during which the row was valid. The start date of each history record must match the end date of the one before it and the end date should be the start date of the next one and so on. The start date of the main table record should equal the last end date of its history. Cleaning up the data in this way will ensure that there are no time gaps.<\/p>\n
To perform the cleanup, follow these three steps:<\/p>\n
Step 1:<\/strong> Find the first history record for each row in the main table and set start date to equal the create date and end date to equal update date.<\/p>\n
UPDATE dbo.region_history SET startdate = createdate, \r\n enddate = updatedate\r\n--select a.regionid,a.regionhistoryid,b.slno\r\nFROM dbo.region_history a INNER JOIN \r\n(SELECT regionid,regionhistoryid,\r\n RANK() OVER (PARTITION BY regionid ORDER BY regionhistoryid) AS slno \r\n FROM dbo.region_history) b\r\nON a.regionid = b.regionid AND a.regionhistoryid = b.regionhistoryid \r\n AND b.slno = 1<\/pre>\n
Step 2:<\/strong> Find the records that are dated after the first one, and update them in sequence, the start date of each record should be equal to end date of the previous one.<\/p>\n
UPDATE dbo.region_history SET startdate = b.priorupdatedate,\r\n enddate = a.updatedate\r\n--select a.*,b.priorupdatedate, b.slno\r\nFROM dbo.region_history a INNER JOIN \r\n(SELECT regionid,regionhistoryid,updatedate,\r\n LAG(updatedate) OVER (PARTITION BY RegionId order by updatedate) \r\n AS priorupdatedate,\r\n RANK() OVER (PARTITION BY regionid ORDER BY regionhistoryid) AS slno \r\n FROM dbo.region_history) b\r\nON a.regionid = b.regionid AND a.regionhistoryid = b.regionhistoryid \r\n AND b.slno > 1 and b.priorupdatedate IS NOT NULL<\/pre>\n
Step 3:<\/strong> The last date of the very last history record should equal the start date in main table of the same record. Remember that you have old triggers still enabled, so any changes you make to the main table will be logged again. So first, you have to drop those triggers. You also have to temporarily remove the period.<\/p>\n
DROP TRIGGER [dbo].[Region_Delete]\r\nDROP TRIGGER [dbo].[Region_Update]\r\nALTER TABLE dbo.region DROP PERIOD FOR system_time;<\/pre>\n
Then, run an update to bridge the history on the history table and main table.<\/p>\n
WITH RegionCTE AS\r\n(\r\n SELECT RegionID, maxupdatedate = MAX(updatedate) \r\n FROM dbo.Region_History GROUP BY regionid\r\n)\r\nUPDATE dbo.region SET startdate = b.maxupdatedate,\r\n enddate = '9999-12-31 23:59:59.9999999'\r\n--select a.*,b.priorstartdate\r\nFROM dbo.region a INNER JOIN RegionCTE b \r\nON a.regionid = b.regionid<\/pre>\n
You may, if you choose to, drop the columns createuser, createdate, updatedate, updateuser, and regionhistoryid from both tables at this point. If you have older queries using these columns, though, this might not be desirable to do.<\/p>\n
Now, when you add the period back in and set versioning on, it works like a charm. You have also covered all time gaps involved so that querying using both the older method and the new method in versioning will work the same.<\/p>\n
ALTER TABLE dbo.Region \r\n ADD PERIOD FOR SYSTEM_TIME ([StartDate], [EndDate])\r\nALTER TABLE dbo.Region \r\n SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Region_History, \r\n DATA_CONSISTENCY_CHECK = ON))\r\nSELECT * FROM dbo.Region \r\nFOR SYSTEM_TIME AS OF '2019-03-10 14:07:29.2366667' ; <\/pre>\n
Gives results as below:<\/p>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2019\/03\/word-image-50.png\")
<\/p>\nDECLARE @ADayAgo datetime2 \r\nSET @ADayAgo = DATEADD (day, -2, getdate()) \r\n\/*Comparison between two points in time for subset of rows*\/ \r\nSELECT D_1_Ago.[RegionID], D.[RegionID], \r\nD_1_Ago.[RegionDescription], D. RegionDescription,\r\nD_1_Ago.[StartDate], D.[StartDate], \r\nD_1_Ago.[EndDate], D.[EndDate] \r\nFROM [dbo].[Region] FOR SYSTEM_TIME AS OF @ADayAgo AS D_1_Ago \r\nJOIN [dbo].[Region] AS D ON D_1_Ago.[RegionID] = [D].[RegionID] \r\nAND D_1_Ago.[RegionID] BETWEEN 1 and 4 ;<\/pre>\n
(The results returned may depend on when you run this query \u2013 in comparison to when the data was created, so use the right date for the variable
@Adayago<\/code>).<\/p>\n![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2019\/03\/word-image-51.png\")
<\/p>\nConverting Your Data<\/h2>\n
Cleaning up the data to make it conform to the system-version temporal tables can be quite tricky, and your scenario may be even more complex. Here are a few things to keep in mind:<\/p>\n
- \n
- The start date should always be less than the end date in both tables.<\/li>\n
- If you have multiple history records for a single parent record in main table, the start and end dates should also be sequential in ascending order with no period overlaps.<\/li>\n
- The end date for the last row in the history table should match the start date for the active record in the parent table.<\/li>\n<\/ul>\n
Deleting data that does not obey these conditions is also a possible solution. Since this destroys the purpose of even having an existing table converted to history, this is not recommended. Instead you could keep that table as is and use a brand-new table to store versioning history.<\/p>\n