- \n
- Slowly changing dimensions.<\/strong> The temporal tables provide a simpler way to querying data that is current for a specified period of time, such as time slicing data, that well-known problem on Data Warehousing databases.<\/li>\n\n\n\n
- Data Auditing.<\/strong> The temporal tables provide an audit trail to determine when data was modified in the \u201cparent\u201d table. This helps to meet the requirements of regulatory compliance<\/strong> and to do data forensics when needed by tracking and auditing data changes over time.<\/li>\n\n\n\n
- Repairing or recovering record level corruptions<\/strong>. Establishing a way of \u2018undoing\u2019 a data change on a table\u2019s row without downtime in case a record is accidentally deleted or updated. Therefore, the previous version of the data can be retrieved from the history table and inserted back into the \u2018parent\u2019 table. \u2013 This helps when someone (or because of some application errors) accidentally deletes data and you want to revert to it or recover it.<\/li>\n\n\n\n
- Reproducing financial reports, invoices and statements<\/strong> with the correct data for the date of issue of the document. Temporal tables allow you to query data as it was at a particular point in time to examine the state of the data as it was then.<\/li>\n\n\n\n
- Analyzing trends<\/strong> by understanding how the data changes over time with the ongoing business activity, and to calculate trends in the way that data changes over time.<\/li>\n<\/ul>\n<\/div>\n\n\n
In the dark days before SQL Server 2016 was introduced, the data-logging mechanism had to be established explicitly in a trigger. To give a simple example, we would need to automate the maintenance of the history for the Department<\/strong> table with following structure, starting with the Department table itself:<\/p>\n\n\n\n
CREATE TABLE dbo.Department\n (\n DeptID INT NOT NULL,\n DeptName VARCHAR(50) NOT NULL,\n ManagerID INT NULL,\n ParentDeptID INT NULL,\n Created DATETIME NOT NULL\n CONSTRAINT DF_Department_Created DEFAULT GETDATE(),\n CONSTRAINT PK_Department_DeptID PRIMARY KEY CLUSTERED(DeptID ASC) ON [PRIMARY]\n ) ON [PRIMARY];\n GO<\/pre>\n\n\n\n
The next step is to create a Department_Log<\/strong> table with two additional columns that provide the history of changes<\/p>\n\n\n
\n- \n
- LogDate<\/li>\n\n\n\n
- LogAction<\/li>\n<\/ul>\n<\/div>\n\n\n
CREATE TABLE dbo.Department_Log\n (\n DeptID INT NOT NULL,\n DeptName VARCHAR(50) NOT NULL,\n ManagerID INT NULL,\n ParentDeptID INT NULL,\n Created DATETIME NOT NULL,\n LogDate DATETIME NOT NULL,\n LogAction VARCHAR(10) NOT NULL\n ) ON [PRIMARY];\n GO<\/pre>\n\n\n\n
When the logging \u2018history\u2019 table is ready, we can create the trigger to log the changes for the UPDATE and DELETE actions:<\/p>\n\n\n\n
CREATE TRIGGER dbo.tr_Department_Log\n ON dbo.Department\n FOR UPDATE, DELETE\n AS\n BEGIN\n SET NOCOUNT ON;\n IF\n (SELECT COUNT(1)\n FROM inserted\n JOIN deleted\n ON Inserted.DeptID = Deleted.DeptID\n ) > 0\n BEGIN\n INSERT dbo.Department_Log\n (DeptID, DeptName, ManagerID, ParentDeptID, Created, LogDate, LogAction)\n SELECT Deleted.DeptID, Deleted.DeptName, Deleted.ManagerID,\n Deleted.ParentDeptID, Deleted.Created, GETDATE(), 'UPDATED'\n FROM deleted;\n END;\n ELSE\n BEGIN\n INSERT dbo.Department_Log\n (DeptID, DeptName, ManagerID, ParentDeptID, Created, LogDate, LogAction)\n SELECT Deleted.DeptID, Deleted.DeptName, Deleted.ManagerID,\n Deleted.ParentDeptID, Deleted.Created, GETDATE(), 'DELETED'\n FROM deleted;\n END;\n SET NOCOUNT OFF;\n END;\n GO<\/pre>\n\n\n\n
To demonstrate how the Department_Log<\/strong> table works with the trigger, I updated three times the row where DeptID = 1, then deleted this row and finally, last update set DeptName column the to its original value.<\/p>\n\n\n\n
update dbo.Department\n\tSET DeptName = ''\nwhere DeptID = 1 \n\nupdate dbo.Department\n\tSET DeptName = 'Engineering IT'\nwhere DeptID = 1 \n\nupdate dbo.Department\n\tSET DeptName = 'Engineering WEB'\nwhere DeptID = 1 \n\nDELETE dbo.Department where DeptID = 1\n\nINSERT dbo.Department(DeptID, DeptName)\nSELECT DeptID,DeptName \nFROM Department_Log WHERE DeptID = 1 and LogAction = 'DELETED'\n\nupdate dbo.Department\n\tSET DeptName = 'Engineering'\nwhere DeptID = 1 \n\nselect DeptID, DeptName,Created,LogDate,LogAction from Department_Log\n<\/pre>\n\n\n\n
The result from Department_Log table shown in the next figure:
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/02\/word-image.png\")
<\/p>\n\n\n\nThe temporal tables feature of SQL Server 2016 can dramatically simplify the logging mechanism. This article provides step-by-step instructions on how to accomplish system-versioned tables.<\/p>\n\n\n\n
To migrate a table into the temporal table, a temporal table option can be set on an existing table. To create a new temporal table, you just need to set the temporal table option to ON (for example, SYSTEM_VERSIONING = ON). When the temporal table option is enabled, SQL Server 2016 generates the \u201chistorical\u201d table automatically, and internally maintains both parent and historical tables, one for storing the actual data and the other for the historical data. The temporal table\u2019s SYSTEM_TIME period columns (for example SysStartTime and SysEndTime) enables the mechanism to query data for a different time slice more efficiently. The updated or deleted data moves into the \u201chistorical\u201d table, whilst the \u201cparent\u201d table keeps the latest row version for updated records.<\/p>\n\n\n\n
What is the catch?<\/h2>\n\n\n\n
The most important considerations, restrictions and limitations of Temporal Tables are:<\/p>\n\n\n
\n- \n
- In order to relate records between the Temporal Table and the history table, you must have a primary key in the Temporal Table. However, the history table cannot have a primary key.<\/li>\n\n\n\n
- The datetime2<\/strong> datatype must be set for the SYSTEM_TIME period columns (for example SysStartTime<\/strong> and SysEndTime<\/strong>).<\/li>\n\n\n\n
- When you create a history table, you must always specify both the schema and table name of the temporal table in the history table.<\/li>\n\n\n\n
- The PAGE compression is the default setting for the history table.<\/li>\n\n\n\n
- The Temporal Tables support blob data types, (nvarchar(max), varchar(max), varbinary(max), ntext, text, and image), that could affect the storage costs and have performance issues.<\/li>\n\n\n\n
- Both temporal and history tables must be created in the same database. You cannot use a Linked Server to provide the Temporal Tables.<\/li>\n\n\n\n
- You cannot use constraints, primary key, foreign keys or column constraints for the history tables.<\/li>\n\n\n\n
- You cannot reference Temporal Tables in indexed views that have queries that use the FOR SYSTEM_TIME clause<\/li>\n\n\n\n
- The SYSTEM_TIME period columns cannot be directly referenced in INSERT and UPDATE statements.<\/li>\n\n\n\n
- You cannot use TRUNCATE TABLE while SYSTEM_VERSIONING is ON.<\/li>\n\n\n\n
- You are not allowed to directly modify the data in a history table.<\/li>\n<\/ul>\n<\/div>\n\n\n
Please reference to Books Online for complete considerations and limitations<\/a> list:<\/p>\n\n\n\n
Creating a Temporal Table<\/h2>\n\n\n\n
I have shown how to create temporal and history tables in one DDL script in Listing 1. As I mentioned earlier, the columns SysStartTime<\/strong> and SysEndTime<\/strong> with data type datetime2 for both column is required for a temporal table. The column SysStartTime<\/strong> has to be GENERATED ALWAYS AS ROW START NOT NULL<\/em> specification, and SysEndTime<\/strong> has to be GENERATED ALWAYS AS ROW END NOT NULL<\/em>. You\u2019re not obliged to provide a default for those columns, but I would recommend it. Both SysStartTime<\/strong> and SysEndTime<\/strong> columns have to be specified in the PERIOD FOR SYSTEM_TIME column (as MSDN defined PERIOD, in other publications PERIOD calls clause).<\/p>\n\n\n\n
Note: The system-versioned columns do not necessarily have to be named as SysStartTime<\/strong> and SysEndTime<\/strong>, but the column names should be selected to reflect the function of time-capturing. The options GENERATED ALWAYS AS ROW START\/END and PERIOD FOR SYSTEM_TIME (nameFrom, nameTo) enables the temporal table feature.<\/p>\n\n\n\n
CREATE TABLE Department\n (\n DeptID INT NOT NULL PRIMARY KEY CLUSTERED,\n DeptName VARCHAR(50) NOT NULL,\n ManagerID INT NULL,\n ParentDeptID INT NULL,\n SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START\n CONSTRAINT DF_Department_SysStartTime DEFAULT SYSUTCDATETIME() NOT NULL,\n SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END\n CONSTRAINT DF_Department_SysEndTime \n \tDEFAULT CONVERT( DATETIME2, '9999-12-31 23:59:59' ) NOT NULL,\n PERIOD FOR SYSTEM_TIME(SysStartTime, SysEndTime)\n )\n WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory));<\/pre>\n\n\n\n
Listing 1: Creating the temporal and history tables<\/p>\n\n\n\n
After the temporal table is created, the underlined history table is created automatically, (Figure 1) as well as a CLUSTERED INDEX with both SysStartTime<\/strong> and SysEndTime<\/strong> (or the name that has been chosen to define system-versioning) column will be created for history table, Listing 2.<\/p>\n\n\n\n
CREATE CLUSTERED INDEX ix_DepartmentHistory\n ON dbo.DepartmentHistory\n (SysStartTime ASC,\n SysEndTime ASC\n ) ON [PRIMARY];<\/pre>\n\n\n\n
Listing 2: Creating the clustered index<\/p>\n\n\n\n
If a new column must be added to the temporal table, then ALTER TABLE \u2026 ADD column DDL needs to be allowed, and the new column will be mirrored automatically on the history table.<\/p>\n\n\n\n
![\"C:\\Users\\AGRINB~1\\AppData\\Local\\Temp\\SNAGHTML8c54d308.PNG\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/02\/c-users-agrinb1-appdata-local-temp-snaghtml8c54d.png\")
<\/figure>\n\n\n\n<\/p>\n\n\n\n
Figure 1: Showing newly created temporal and history tables in Object Explorer.<\/p>\n\n\n\n
However, it\u2019s not possible to use DROP TABLE DDL for a temporal table. First, SYSTEM_VERSIONING has to be turned OFF.<\/p>\n\n\n\n
ALTER TABLE Department SET (SYSTEM_VERSIONING = OFF); <\/pre>\n\n\n\n
Listing 3: Disabling SYSTEM_VERSIONING on Department table.<\/p>\n\n\n\n
When SYSTEM_VERSIONING is set to OFF, both temporal and history tables become regular tables. The DROP TABLE command can then be used for those tables.<\/p>\n\n\n\n
Read also:<\/strong>
Temporary tables in SQL Server<\/a>
In-memory optimized tables in SQL Server<\/a>
Converting a database to In-Memory OLTP<\/a><\/p>\n\n\n\nSetting existing table to system-versioned table<\/h2>\n\n\n\n
SQL Server allows you to convert an existing table into a temporal table. In order to perform this task, you need to make sure that a primary key exists on the table, and if it does not already exist, then you need to create one. Then the table has to be altered with two datetime2<\/strong> data type columns and also the option GENERATED ALWAYS AS ROW START\/END has to be applied with \u2026
PERIOD FOR SYSTEM_TIME (nameFrom, nameTo).
Both options have to be completed with ALTER command. The second ALTER command enables the SYSTEM_VERSIONING property, and optionally (I recommend providing it explicitly), provide a name for the HISTORY_TABLE property, Listing 4.<\/p>\n\n\n\nFor example, let\u2019s set the existing table Department_Exist<\/strong> to a temporal table. Run Listing 4, then run Listing 5. Refresh the Table icon to see the result, as in Figure 2.<\/p>\n\n\n\n
CREATE TABLE Department_Exist ( \n DeptID int NOT NULL PRIMARY KEY CLUSTERED \n , DeptName varchar(50) NOT NULL \n , ManagerID INT NULL \n , ParentDeptID int NULL ) <\/pre>\n\n\n\n
Listing 4: Creating table Department_Exist.<\/p>\n\n\n\n
ALTER TABLE dbo.Department_Exist\n ADD SysStartTime datetime2 GENERATED ALWAYS AS ROW START \n CONSTRAINT DF_Department_Exist_SysStartTime DEFAULT SYSUTCDATETIME() NOT NULL,\n SysEndTime datetime2 GENERATED ALWAYS AS ROW END \n CONSTRAINT DF_Department_Exist_SysEndTime DEFAULT CONVERT (DATETIME2, '9999-12-31 23:59:59') NOT NULL,\n PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)\n GO\n \n ALTER TABLE dbo.Department_Exist\n SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Department_ExistHistory))\n GO<\/pre>\n\n\n\n
Listing 5: Adding the system-versioned columns, and enable the system-versioned on the Department_Exist<\/strong> table.<\/p>\n\n\n\n
- Data Auditing.<\/strong> The temporal tables provide an audit trail to determine when data was modified in the \u201cparent\u201d table. This helps to meet the requirements of regulatory compliance<\/strong> and to do data forensics when needed by tracking and auditing data changes over time.<\/li>\n\n\n\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/02\/word-image-1.png\")
<\/figure>![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/02\/word-image-2.png\")
<\/figure>