{"id":27440,"date":"2023-08-02T06:30:00","date_gmt":"2023-08-02T04:30:00","guid":{"rendered":"https:\/\/expert-only.com\/?p=27440"},"modified":"2023-10-10T18:30:43","modified_gmt":"2023-10-10T16:30:43","slug":"how-to-use-sql-server-temporal-tables","status":"publish","type":"post","link":"https:\/\/expert-only.com\/en\/t-sql\/how-to-use-sql-server-temporal-tables\/","title":{"rendered":"How to use SQL Server temporal tables?"},"content":{"rendered":"\n<h4 class=\"wp-block-heading\" id=\"h-introduction-to-sql-server-temporal-tables-to-manage-data-versioning-automatically\"><strong><em>Introduction to SQL Server Temporal Tables to manage data versioning automatically. <\/em><\/strong><\/h4>\n\n\n\n<p>Temporal tables in SQL Server, also known as system-versioned tables, are a feature introduced in MS SQL Server 2016. They provide built-in support for storing historical data related to changes made to data in a table. This feature is beneficial for auditing or business data analytics purposes, where it&#8217;s crucial to understand the state of data at any given point in time. And also for all companies subject to regular regulatory reporting with legal transparency obligations.<\/p>\n\n\n\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_82_2 ez-toc-wrap-center counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/expert-only.com\/en\/t-sql\/how-to-use-sql-server-temporal-tables\/#basic-ms-sql-temporal-table-syntax\" >Basic MS SQL temporal table syntax<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/expert-only.com\/en\/t-sql\/how-to-use-sql-server-temporal-tables\/#update-sql-data-in-temporal-tables\" >Update SQL data in temporal tables<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/expert-only.com\/en\/t-sql\/how-to-use-sql-server-temporal-tables\/#delete-data-from-system-versioned-tables\" >Delete data from system-versioned tables<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/expert-only.com\/en\/t-sql\/how-to-use-sql-server-temporal-tables\/#t-sql-query-syntax-to-check-data-from-temporal-tables\" >T-SQL query syntax to check data from temporal tables<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/expert-only.com\/en\/t-sql\/how-to-use-sql-server-temporal-tables\/#test-a-temporal-data-to-see-how-it-works\" >Test a temporal data to see how it works<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/expert-only.com\/en\/t-sql\/how-to-use-sql-server-temporal-tables\/#modify-an-existing-system-versioned-tables\" >Modify an existing system-versioned tables<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/expert-only.com\/en\/t-sql\/how-to-use-sql-server-temporal-tables\/#delete-a-system-versioned-table\" >Delete a system-versioned table<\/a><\/li><\/ul><\/nav><\/div>\n<h2 class=\"wp-block-heading\" id=\"h-basic-ms-sql-temporal-table-syntax\"><span class=\"ez-toc-section\" id=\"basic-ms-sql-temporal-table-syntax\"><\/span>Basic MS SQL temporal table syntax<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Let&#8217;s start by creating a basic temporal table in Transact-SQL. So the T-SQL code below <strong>creates a table named Employee with system-versioning<\/strong>. The columns <em>SysStartTime<\/em> and <em>SysEndTime<\/em> are used to record the period of each row. To make a small recap, 3 additional elements are necessary to create and manage a temporal SQL Server table: <\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong><em>SysStartTime<\/em><\/strong> system column to mark the start of the period, only supports the DATETIME2 data type.<\/li>\n\n\n\n<li><strong><em>SysEndTime<\/em><\/strong> to mark the end, only supports the DATETIME2 data type.<\/li>\n\n\n\n<li>A system history table, here it is named <strong><em>dbo.EmployeeHistory<\/em><\/strong>, with same structure, except the keys.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-the-create-table-statement-is-specific\">The create table statement is specific<\/h3>\n\n\n\n<p>The table definition is special, however, and that&#8217;s the goal, inserting data into a system-versioned table is similar to inserting data into any other SQL Server table. Indeed, <strong>system-versioned tables, i.e. temporal table are designed to automatically manage data versioning<\/strong>, particularly useful to mange dimensions, and slowly changing dimensions, called <a href=\"https:\/\/www.oracle.com\/webfolder\/technetwork\/tutorials\/obe\/db\/10g\/r2\/owb\/owb10gr2_gs\/owb\/lesson3\/slowlychangingdimensions.htm\" target=\"_blank\" rel=\"noreferrer noopener\">SCD<\/a>.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"mssql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">CREATE TABLE [Expert-Only].dbo.Employee\n(\n   EmployeeId INT PRIMARY KEY,\n   EmployeeName NVARCHAR(100),\n   Position NVARCHAR(100),\n   Salary DECIMAL(18, 2),\n   SysStartTime  DATETIME2 GENERATED ALWAYS AS ROW START,\n   SysEndTime    DATETIME2 GENERATED ALWAYS AS ROW END,\n   PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)\n)\nWITH (SYSTEM_VERSIONING = ON (\n   HISTORY_TABLE = dbo.EmployeeHistory)\n);<\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-insert-data-into-the-temporal-table\">Insert data into the temporal table<\/h3>\n\n\n\n<p>So this basic <a href=\"https:\/\/expert-only.com\/en\/t-sql\/sql-server-insert-into-from-a-select\/\"><strong>T-SQL INSERT statement<\/strong><\/a> will insert a new row into the Employee table, and it will update the history table associated when the data change.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"mssql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">INSERT INTO [Expert-Only].dbo.Employee (EmployeeId, EmployeeName, Position, Salary)\nVALUES (1, 'John Doe', 'Developer', 80000);<\/pre>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"800\" height=\"540\" src=\"https:\/\/expert-only.com\/wp-content\/uploads\/2023\/10\/create-sql-server-temporal-table.jpg\" alt=\"Create a SQL Server system-versioned temporal table and insert data\" class=\"wp-image-27467\" srcset=\"https:\/\/expert-only.com\/wp-content\/uploads\/2023\/10\/create-sql-server-temporal-table.jpg 800w, https:\/\/expert-only.com\/wp-content\/uploads\/2023\/10\/create-sql-server-temporal-table-300x203.jpg 300w, https:\/\/expert-only.com\/wp-content\/uploads\/2023\/10\/create-sql-server-temporal-table-768x518.jpg 768w\" sizes=\"auto, (max-width: 800px) 100vw, 800px\" \/><figcaption class=\"wp-element-caption\"><em>Create a SQL Server system-versioned temporal table and insert data<\/em><\/figcaption><\/figure><\/div>\n\n\n<h3 class=\"wp-block-heading\" id=\"h-system-versioned-and-history-table-s-structure\">System-versioned and history table&#8217;s structure<\/h3>\n\n\n\n<p>As you can see in the screenshot below, the <strong><em>Employee<\/em><\/strong> table is now system-versioned and the <strong><em>EmployeeHistory<\/em><\/strong> table is located just behind it in the SSMS explorer. <strong>Both tables has the same structure, except of course the primary keys<\/strong>, because the history table needs to stores multiple version of lines with the same IDs. <\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/expert-only.com\/wp-content\/uploads\/2023\/10\/sql-server-System-Versioned-and-history-table-structure.jpg\" alt=\"History table under the system-versioned table \" class=\"wp-image-27474\" style=\"width:439px;height:678px\" width=\"439\" height=\"678\" srcset=\"https:\/\/expert-only.com\/wp-content\/uploads\/2023\/10\/sql-server-System-Versioned-and-history-table-structure.jpg 440w, https:\/\/expert-only.com\/wp-content\/uploads\/2023\/10\/sql-server-System-Versioned-and-history-table-structure-194x300.jpg 194w\" sizes=\"auto, (max-width: 439px) 100vw, 439px\" \/><figcaption class=\"wp-element-caption\"><em>History table under the system-versioned table <\/em><\/figcaption><\/figure><\/div>\n\n\n<h2 class=\"wp-block-heading\" id=\"h-update-sql-data-in-temporal-tables\"><span class=\"ez-toc-section\" id=\"update-sql-data-in-temporal-tables\"><\/span>Update SQL data in temporal tables<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>When you update a record in a system-versioned table, SQL Server automatically inserts a copy of the old row into the history table. So you can analyse any past change to better understand your data. As a result, after executing this SQL command, the <em>EmployeeHistory <\/em>table will have a record with the old salary. To illustrate the concept, the following query triggers an insert with the old value of the line, directly inside the history table.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"mssql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">UPDATE [Expert-Only].dbo.Employee\n   SET Salary = 85000\n   WHERE EmployeeId = 1;\n<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-delete-data-from-system-versioned-tables\"><span class=\"ez-toc-section\" id=\"delete-data-from-system-versioned-tables\"><\/span>Delete data from system-versioned tables<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Deleting records in a system-versioned table will not remove the data entirely. The deleted records will be moved to the history table. After running the T-SQL command below, the <strong><em>EmployeeHistory<\/em><\/strong> table will have the deleted record.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"mssql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">DELETE FROM Employee\n   WHERE EmployeeId = 1;\n<\/pre>\n\n\n\n<p>So of course you need to be aware of how much often the data changes, and also about the data volumes to avoid ending with huge databases and backups. You can see it like the same principle of the <strong><a href=\"https:\/\/expert-only.com\/en\/ms-dos\/delete-windows-file-using-cmd\/\">Windows<\/a><\/strong> Recycle Bin, indeed you can have multiple versions of the same file, deleted but still in the bin, till you empty it.<\/p>\n\n\n\n<p><strong><em>A solution to this is to put in place a purge system that periodically delete old records from the history table.<\/em><\/strong><\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-t-sql-query-syntax-to-check-data-from-temporal-tables\"><span class=\"ez-toc-section\" id=\"t-sql-query-syntax-to-check-data-from-temporal-tables\"><\/span>T-SQL query syntax to check data from temporal tables<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p><strong>SQL Server provides the FOR SYSTEM_TIME clause to query data from a system-versioned table. <\/strong>This SQL command will return the data as it was at the specified point in time. You can use more details of course to be more specific. You can go till <\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"mssql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">SELECT *\n  FROM Employee\n  FOR SYSTEM_TIME AS OF '2023-10-10';\n\nSELECT *\n  FROM Employee\n  FOR SYSTEM_TIME AS OF '2023-10-10 15:30:45.1234567';\n\n<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-test-a-temporal-data-to-see-how-it-works\"><span class=\"ez-toc-section\" id=\"test-a-temporal-data-to-see-how-it-works\"><\/span>Test a temporal data to see how it works<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>To do a more in depth test, lets recap all actions performed, and add one, in this order to see if the table properly keeps track of our changes. Between each steps, wait a few minutes for better visibility in the history table.<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Insert the first line with a salary of <em>80&#8217;000<\/em> dollars (made in second section of the tutorial)<\/li>\n\n\n\n<li>Update the salary to <em>85&#8217;000<\/em> dollars<\/li>\n\n\n\n<li>Delete the Employee from the table. <\/li>\n\n\n\n<li>Insert again the same employee but this time with a <em>90&#8217;000<\/em> $ salary and a <strong><em>Developer Senior<\/em><\/strong> role.<\/li>\n\n\n\n<li>Delete again the Employee from the table. <strong><em>Not visible on the Screenshot<\/em><\/strong> but available in the T-SQL code below. <\/li>\n<\/ol>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"mssql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">UPDATE [Expert-Only].dbo.Employee\n   SET Salary = 85000\n   WHERE EmployeeId = 1;\n\n-- *** Wait a few minutes *** -- \nDELETE FROM [Expert-Only].dbo.Employee\n   WHERE EmployeeId = 1;\n\n-- *** Wait a few minutes *** -- \nINSERT INTO [Expert-Only].dbo.Employee\n   (EmployeeId, EmployeeName, Position, Salary)\nVALUES (1, 'John Doe', 'Developer Senior', 90000);\n\n-- *** Wait a few minutes *** -- \nDELETE FROM [Expert-Only].dbo.Employee\n   WHERE EmployeeId = 1;\n\nSELECT * FROM dbo.EmployeeHistory;\n\n-- First insert\nSELECT * FROM [Expert-Only].dbo.Employee\n  FOR SYSTEM_TIME AS OF '2023-10-10 10:56:00';\n\n-- Update to 85000 $\nSELECT * FROM [Expert-Only].dbo.Employee\n  FOR SYSTEM_TIME AS OF '2023-10-10 11:03:00';\n\n-- Second insert as Developper Senior\nSELECT * FROM [Expert-Only].dbo.Employee\n  FOR SYSTEM_TIME AS OF '2023-10-10 11:06:00';\n<\/pre>\n\n\n\n<p>Then check the result using SSMS to select the content of the history table and also the Employee table art very specific points in time. As a result, the Employee table is now empty. the three archived lines from the <em>EmployeeHistory<\/em> table are available using a SELECT statement on the Employee table itself in 3 different points in time.  <\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/expert-only.com\/wp-content\/uploads\/2023\/10\/test-sql-server-temporal-table-with-history-check.jpg\" alt=\"Data from the history table is visible in the temporal versioned table.\" class=\"wp-image-27478\" style=\"width:741px;height:980px\" width=\"741\" height=\"980\" srcset=\"https:\/\/expert-only.com\/wp-content\/uploads\/2023\/10\/test-sql-server-temporal-table-with-history-check.jpg 741w, https:\/\/expert-only.com\/wp-content\/uploads\/2023\/10\/test-sql-server-temporal-table-with-history-check-227x300.jpg 227w\" sizes=\"auto, (max-width: 741px) 100vw, 741px\" \/><figcaption class=\"wp-element-caption\"><em>Data from the history table is visible in the temporal versioned table.<\/em><\/figcaption><\/figure><\/div>\n\n\n<h2 class=\"wp-block-heading\" id=\"h-modify-an-existing-system-versioned-tables\"><span class=\"ez-toc-section\" id=\"modify-an-existing-system-versioned-tables\"><\/span>Modify an existing system-versioned tables<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Of course, as data models evolves in the database over time, you need to maintain the history table when the versioned table changes. To modify the schema of a system-versioned table, for example to add a new column to the Employee table, use these 3 steps, in this order: <\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>First turn off the system versioning<\/li>\n\n\n\n<li>Make the changes to the temporal table<\/li>\n\n\n\n<li>Then turn the system versioning back on.<\/li>\n<\/ol>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"mssql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">ALTER TABLE Employee SET (SYSTEM_VERSIONING = OFF);\n\nALTER TABLE Employee ADD Email NVARCHAR(100);\n\nALTER TABLE Employee SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));\n <\/pre>\n\n\n\n<p><\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-delete-a-system-versioned-table\"><span class=\"ez-toc-section\" id=\"delete-a-system-versioned-table\"><\/span>Delete a system-versioned table<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>As it is a specific type of table, to delete it, you also need in this case to stop the versioning manually before dropping the table. <strong>To drop a system-versioned table without error, use the generate script option from SSMS to generate the complete statement.<\/strong> As you can see below, it performs 3 operations: <\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Stop the versioning<\/li>\n\n\n\n<li>Drop the system-versioned table<\/li>\n\n\n\n<li>Drop the temporal table that contains the history<\/li>\n<\/ol>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"mssql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">USE [Expert-Only]\nGO\n\nIF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Employee]') AND type in (N'U'))\nALTER TABLE [dbo].[Employee] SET ( SYSTEM_VERSIONING = OFF  )\nGO\n\nDROP TABLE IF EXISTS [dbo].[Employee]\nGO\n\nDROP TABLE IF EXISTS [dbo].[EmployeeHistory]\nGO\n<\/pre>\n\n\n\n<p>If you try to drop the table without stopping the versioning, the system returns this error:<\/p>\n\n\n\n<p>Msg 13552, Level 16, State 1, Line 1<br><em>Drop table operation failed on table &#8216;Expert-Only.dbo.Employee&#8217; because it is not a supported operation on system-versioned temporal tables.<\/em><\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-conclusion-on-setting-up-sql-server-temporal-tables\">Conclusion on setting-up SQL Server temporal tables<\/h3>\n\n\n\n<p>In conclusion, SQL Server Temporal Tables provide a built-in solution for keeping track of data changes over time. They can be valuable in situations where auditing or data analysis is required. For enterprise real cases scenarios, when you had to do all the archiving steps with custom code or SCD, it was complex and heavy to maintain. Using this system-versioned table features makes it much more straightforward to implement.<\/p>\n\n\n\n<p>By understanding how to create, insert, update, delete, and query data from these tables, one can leverage this powerful feature in MS SQL Server. To go further and discover more uses cases for temporal tables, a typical real life example is when <a href=\"https:\/\/expert-only.com\/en\/ssis\/import-csv-file-into-sql-server-with-ssis\/\">loading a datawarehouse dimension table using an package SSIS<\/a>.<\/p>\n\n\n\n<figure class=\"wp-block-embed aligncenter is-type-wp-embed is-provider-sql-and-it-tutorials wp-block-embed-sql-and-it-tutorials\"><div class=\"wp-block-embed__wrapper\">\n<blockquote class=\"wp-embedded-content\" data-secret=\"Fh4M9uYPoj\"><a href=\"https:\/\/expert-only.com\/en\/ssis\/import-csv-file-into-sql-server-with-ssis\/\">Import CSV file into SQL Server using SSIS<\/a><\/blockquote><iframe loading=\"lazy\" class=\"wp-embedded-content\" sandbox=\"allow-scripts\" security=\"restricted\" style=\"position: absolute; clip: rect(1px, 1px, 1px, 1px);\" title=\"&#8220;Import CSV file into SQL Server using SSIS&#8221; &#8212; SQL and IT Tutorials\" src=\"https:\/\/expert-only.com\/en\/ssis\/import-csv-file-into-sql-server-with-ssis\/embed\/#?secret=KgGmtaPkp3#?secret=Fh4M9uYPoj\" data-secret=\"Fh4M9uYPoj\" width=\"500\" height=\"282\" frameborder=\"0\" marginwidth=\"0\" marginheight=\"0\" scrolling=\"no\"><\/iframe>\n<\/div><\/figure>\n\n\n\n<p>For more detailed information on working with Temporal Tables in SQL Server, check out the official <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/tables\/temporal-tables?view=sql-server-ver15\" target=\"_blank\" rel=\"noreferrer noopener nofollow\">Microsoft documentation<\/a>.<\/p>\n\n\n\n<p>Another tutorial around the same topics that may be of interest to you is <strong><a href=\"https:\/\/expert-only.com\/en\/t-sql\/how-to-use-sql-server-json-functions\/\">how to handle the JSON data type using native SQL Server functions<\/a><\/strong>.<\/p>\n","protected":false},"excerpt":{"rendered":"<div class=\"mh-excerpt\"><p>Introduction to SQL Server Temporal Tables to manage data versioning automatically. Temporal tables in SQL Server, also known as system-versioned tables, are a feature introduced in MS SQL Server 2016. They provide built-in support for storing historical data related <a class=\"mh-excerpt-more\" href=\"https:\/\/expert-only.com\/en\/t-sql\/how-to-use-sql-server-temporal-tables\/\" title=\"How to use SQL Server temporal tables?\">&#8230;<\/a><\/p>\n<\/div>","protected":false},"author":1,"featured_media":10619,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[454],"tags":[],"class_list":{"0":"post-27440","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-t-sql"},"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v21.7 (Yoast SEO v26.2) - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>How to use SQL Server temporal tables? T-SQL<\/title>\n<meta name=\"description\" content=\"Use SQL Server temporal tables to manage data versioning automatically and track the deletes, updates and inserts using system-versioning.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/expert-only.com\/en\/t-sql\/how-to-use-sql-server-temporal-tables\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to use SQL Server temporal tables?\" \/>\n<meta property=\"og:description\" content=\"Use SQL Server temporal tables to manage data versioning automatically and track the deletes, updates and inserts using system-versioning.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/expert-only.com\/en\/t-sql\/how-to-use-sql-server-temporal-tables\/\" \/>\n<meta property=\"og:site_name\" content=\"SQL and IT Tutorials\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/ExpertOnlyCom\/\" \/>\n<meta property=\"article:published_time\" content=\"2023-08-02T04:30:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-10-10T16:30:43+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/digitization-FD44BBF7CF9_1920x1080.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1920\" \/>\n\t<meta property=\"og:image:height\" content=\"1080\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Expert-Only\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@expert_only\" \/>\n<meta name=\"twitter:site\" content=\"@expert_only\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Expert-Only\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/how-to-use-sql-server-temporal-tables\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/how-to-use-sql-server-temporal-tables\/\"},\"author\":{\"name\":\"Expert-Only\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef\"},\"headline\":\"How to use SQL Server temporal tables?\",\"datePublished\":\"2023-08-02T04:30:00+00:00\",\"dateModified\":\"2023-10-10T16:30:43+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/how-to-use-sql-server-temporal-tables\/\"},\"wordCount\":1173,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/expert-only.com\/en\/#organization\"},\"image\":{\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/how-to-use-sql-server-temporal-tables\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/digitization-FD44BBF7CF9_1920x1080.jpg\",\"articleSection\":[\"T-SQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/expert-only.com\/en\/t-sql\/how-to-use-sql-server-temporal-tables\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/how-to-use-sql-server-temporal-tables\/\",\"url\":\"https:\/\/expert-only.com\/en\/t-sql\/how-to-use-sql-server-temporal-tables\/\",\"name\":\"How to use SQL Server temporal tables? T-SQL\",\"isPartOf\":{\"@id\":\"https:\/\/expert-only.com\/en\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/how-to-use-sql-server-temporal-tables\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/how-to-use-sql-server-temporal-tables\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/digitization-FD44BBF7CF9_1920x1080.jpg\",\"datePublished\":\"2023-08-02T04:30:00+00:00\",\"dateModified\":\"2023-10-10T16:30:43+00:00\",\"description\":\"Use SQL Server temporal tables to manage data versioning automatically and track the deletes, updates and inserts using system-versioning.\",\"breadcrumb\":{\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/how-to-use-sql-server-temporal-tables\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/expert-only.com\/en\/t-sql\/how-to-use-sql-server-temporal-tables\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/how-to-use-sql-server-temporal-tables\/#primaryimage\",\"url\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/digitization-FD44BBF7CF9_1920x1080.jpg\",\"contentUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/digitization-FD44BBF7CF9_1920x1080.jpg\",\"width\":1920,\"height\":1080},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/how-to-use-sql-server-temporal-tables\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"en\",\"item\":\"https:\/\/expert-only.com\/en\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to use SQL Server temporal tables?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/expert-only.com\/en\/#website\",\"url\":\"https:\/\/expert-only.com\/en\/\",\"name\":\"SQL and IT Tutorials\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/expert-only.com\/en\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/expert-only.com\/en\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/expert-only.com\/en\/#organization\",\"name\":\"Expert-Only\",\"url\":\"https:\/\/expert-only.com\/en\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg\",\"contentUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg\",\"width\":381,\"height\":174,\"caption\":\"Expert-Only\"},\"image\":{\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/ExpertOnlyCom\/\",\"https:\/\/x.com\/expert_only\",\"https:\/\/www.youtube.com\/channel\/UCMS5sR_FwAetB0FmciNvUaA\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef\",\"name\":\"Expert-Only\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g\",\"caption\":\"Expert-Only\"}}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"How to use SQL Server temporal tables? T-SQL","description":"Use SQL Server temporal tables to manage data versioning automatically and track the deletes, updates and inserts using system-versioning.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/expert-only.com\/en\/t-sql\/how-to-use-sql-server-temporal-tables\/","og_locale":"en_US","og_type":"article","og_title":"How to use SQL Server temporal tables?","og_description":"Use SQL Server temporal tables to manage data versioning automatically and track the deletes, updates and inserts using system-versioning.","og_url":"https:\/\/expert-only.com\/en\/t-sql\/how-to-use-sql-server-temporal-tables\/","og_site_name":"SQL and IT Tutorials","article_publisher":"https:\/\/www.facebook.com\/ExpertOnlyCom\/","article_published_time":"2023-08-02T04:30:00+00:00","article_modified_time":"2023-10-10T16:30:43+00:00","og_image":[{"width":1920,"height":1080,"url":"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/digitization-FD44BBF7CF9_1920x1080.jpg","type":"image\/jpeg"}],"author":"Expert-Only","twitter_card":"summary_large_image","twitter_creator":"@expert_only","twitter_site":"@expert_only","twitter_misc":{"Written by":"Expert-Only","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/expert-only.com\/en\/t-sql\/how-to-use-sql-server-temporal-tables\/#article","isPartOf":{"@id":"https:\/\/expert-only.com\/en\/t-sql\/how-to-use-sql-server-temporal-tables\/"},"author":{"name":"Expert-Only","@id":"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef"},"headline":"How to use SQL Server temporal tables?","datePublished":"2023-08-02T04:30:00+00:00","dateModified":"2023-10-10T16:30:43+00:00","mainEntityOfPage":{"@id":"https:\/\/expert-only.com\/en\/t-sql\/how-to-use-sql-server-temporal-tables\/"},"wordCount":1173,"commentCount":0,"publisher":{"@id":"https:\/\/expert-only.com\/en\/#organization"},"image":{"@id":"https:\/\/expert-only.com\/en\/t-sql\/how-to-use-sql-server-temporal-tables\/#primaryimage"},"thumbnailUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/digitization-FD44BBF7CF9_1920x1080.jpg","articleSection":["T-SQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/expert-only.com\/en\/t-sql\/how-to-use-sql-server-temporal-tables\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/expert-only.com\/en\/t-sql\/how-to-use-sql-server-temporal-tables\/","url":"https:\/\/expert-only.com\/en\/t-sql\/how-to-use-sql-server-temporal-tables\/","name":"How to use SQL Server temporal tables? T-SQL","isPartOf":{"@id":"https:\/\/expert-only.com\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/expert-only.com\/en\/t-sql\/how-to-use-sql-server-temporal-tables\/#primaryimage"},"image":{"@id":"https:\/\/expert-only.com\/en\/t-sql\/how-to-use-sql-server-temporal-tables\/#primaryimage"},"thumbnailUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/digitization-FD44BBF7CF9_1920x1080.jpg","datePublished":"2023-08-02T04:30:00+00:00","dateModified":"2023-10-10T16:30:43+00:00","description":"Use SQL Server temporal tables to manage data versioning automatically and track the deletes, updates and inserts using system-versioning.","breadcrumb":{"@id":"https:\/\/expert-only.com\/en\/t-sql\/how-to-use-sql-server-temporal-tables\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/expert-only.com\/en\/t-sql\/how-to-use-sql-server-temporal-tables\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/expert-only.com\/en\/t-sql\/how-to-use-sql-server-temporal-tables\/#primaryimage","url":"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/digitization-FD44BBF7CF9_1920x1080.jpg","contentUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/digitization-FD44BBF7CF9_1920x1080.jpg","width":1920,"height":1080},{"@type":"BreadcrumbList","@id":"https:\/\/expert-only.com\/en\/t-sql\/how-to-use-sql-server-temporal-tables\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"en","item":"https:\/\/expert-only.com\/en\/"},{"@type":"ListItem","position":2,"name":"How to use SQL Server temporal tables?"}]},{"@type":"WebSite","@id":"https:\/\/expert-only.com\/en\/#website","url":"https:\/\/expert-only.com\/en\/","name":"SQL and IT Tutorials","description":"","publisher":{"@id":"https:\/\/expert-only.com\/en\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/expert-only.com\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/expert-only.com\/en\/#organization","name":"Expert-Only","url":"https:\/\/expert-only.com\/en\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/","url":"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg","contentUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg","width":381,"height":174,"caption":"Expert-Only"},"image":{"@id":"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/ExpertOnlyCom\/","https:\/\/x.com\/expert_only","https:\/\/www.youtube.com\/channel\/UCMS5sR_FwAetB0FmciNvUaA"]},{"@type":"Person","@id":"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef","name":"Expert-Only","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/expert-only.com\/en\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g","caption":"Expert-Only"}}]}},"_links":{"self":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts\/27440","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/comments?post=27440"}],"version-history":[{"count":52,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts\/27440\/revisions"}],"predecessor-version":[{"id":27534,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts\/27440\/revisions\/27534"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/media\/10619"}],"wp:attachment":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/media?parent=27440"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/categories?post=27440"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/tags?post=27440"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}