{"id":685,"date":"2024-01-21T15:07:00","date_gmt":"2024-01-21T15:07:00","guid":{"rendered":"https:\/\/eipsoftware.com\/musings\/?p=685"},"modified":"2024-04-21T15:11:34","modified_gmt":"2024-04-21T15:11:34","slug":"temporal-tables","status":"publish","type":"post","link":"https:\/\/eipsoftware.com\/musings\/temporal-tables\/","title":{"rendered":"Temporal Tables"},"content":{"rendered":"\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p class=\"wp-block-paragraph\">Where we are going, we need to keep track of the history in the tables.<\/p>\n<\/blockquote>\n\n\n\n<p class=\"wp-block-paragraph\">How to set up and use Temporal Tables in Microsoft SQL  Microsoft SQL has native support for keeping track of Insert \/ Update \/ Delete from system versioned tables. The following explains how to set up and use temporal tables in Microsoft SQL.<\/p>\n\n\n\n<!--more-->\n\n\n\n<div class=\"wp-block-getwid-table-of-contents is-style-default\"><ul class=\"wp-block-getwid-table-of-contents__list\"><li><a href=\"#Temporal-Table\">Temporal Table<\/a><ul><li><a href=\"#Create-Schema\">Create Schema<\/a><\/li><li><a href=\"#Create-Table\">Create Table<\/a><\/li><li><a href=\"#Alter-Existing-Table\">Alter Existing Table<\/a><\/li><li><a href=\"#Populate-Table\">Populate Table<\/a><ul><li><a href=\"#View-Initial-Data\">View Initial Data<\/a><\/li><\/ul><\/li><li><a href=\"#Update-the-Address-Table\">Update the Address Table<\/a><ul><li><a href=\"#View-Updated-Data\">View Updated Data<\/a><\/li><li><a href=\"#View-Historical-Data\">View Historical Data<\/a><\/li><\/ul><\/li><li><a href=\"#Select-Values-as-of-Specified-Date\">Select Values as of Specified Date<\/a><\/li><\/ul><\/li><li><a href=\"#Join-Temporal-Tables\">Join Temporal Tables<\/a><ul><li><a href=\"#Create-a-Second-Temporal-Table\">Create a Second Temporal Table<\/a><\/li><li><a href=\"#Populate-with-Initial-Values\">Populate with Initial Values<\/a><\/li><li><a href=\"#Update-the-Record\">Update the Record<\/a><ul><li><a href=\"#View-Current-and-Historical-Records\">View Current and Historical Records<\/a><\/li><\/ul><\/li><li><a href=\"#Modify-Company-History-Records\">Modify Company History Records<\/a><ul><li><a href=\"#View-the-Changes\">View the Changes<\/a><\/li><\/ul><\/li><li><a href=\"#Modify-the-Home-Address-History-Table\">Modify the Home Address History Table<\/a><ul><li><a href=\"#View-the-Changes.1\">View the Changes<\/a><\/li><\/ul><\/li><\/ul><\/li><li><a href=\"#Query-the-Temporal-Tables\">Query the Temporal Tables<\/a><ul><li><a href=\"#Modifiers-for-History-Table\">Modifiers for History Table<\/a><\/li><\/ul><\/li><li><a href=\"#gcf486f353224\">Examples<\/a><ul><li><a href=\"#Example-1\">Example 1<\/a><\/li><li><a href=\"#Example-2\">Example 2<\/a><\/li><li><a href=\"#Example-3\">Example 3<\/a><\/li><\/ul><\/li><li><a href=\"#Remove-Temporal-Tables\">Remove Temporal Tables<\/a><\/li><\/ul><\/div>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"Temporal-Table\">Temporal Table<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"Create-Schema\">Create Schema<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Create a schema that will hold all of the history tables.<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">USE Sandbox;\nGO\nIF NOT EXISTS (SELECT [name] FROM Sandbox.sys.schemas WHERE [name] = 'history')\nBEGIN\n\tDECLARE @sql NVarchar(64) = 'CREATE\tSCHEMA history  AUTHORIZATION [DBO];'\n\tEXECUTE sp_executesql @sql;\nEND\nGO<\/pre><\/div>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><\/div>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"Create-Table\">Create Table<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Create the first table that will have history, will load sample data from AdventureWorks database.<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">IF EXISTS (\n\t\t  SELECT\t[TABLE_NAME]\n\t\t  FROM\t\tINFORMATION_SCHEMA.TABLES\n\t\t  WHERE\t\tTABLE_TYPE\t\t   = 'BASE TABLE'\n\t\t\t\t\tAND [TABLE_NAME]   = 'HomeAddress'\n\t\t\t\t\tAND [TABLE_SCHEMA] = 'dbo'\n\t\t  )\nBEGIN\n\tALTER TABLE dbo.HomeAddress SET (SYSTEM_VERSIONING = OFF);\n\tTRUNCATE TABLE dbo.HomeAddress;\n\tALTER TABLE dbo.HomeAddress SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = history.HomeAddress));\nEND;\nELSE\nBEGIN\n\tCREATE TABLE dbo.HomeAddress\n\t(\n\t\tAddressID\t   Int\t\t\t NOT NULL PRIMARY KEY CLUSTERED\n\t\t, AddressLine1 NVarchar(128) NULL\n\t\t, City\t\t   NVarchar(32)\t NULL\n\t\t, ProvinceCode NVarchar(8)\t NULL\n\t\t, ProvinceName NVarchar(64)\t NULL\n\t\t, CountryName  NVarchar(64)\t NULL\n\t\t, PostalCode   NVarchar(16)\t NULL\n\t\t-- can add HIDDEN flag when creating columns\n\t\t, EffectiveFrom\t\tDateTime2\t\tGENERATED ALWAYS AS ROW START NOT NULL\t\n\t\t, EffectiveTo\t\tDateTime2\t\tGENERATED ALWAYS AS ROW END NOT NULL\n\t\t, PERIOD FOR SYSTEM_TIME (EffectiveFrom, EffectiveTo)\n\t)\n\tWITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = history.HomeAddress));\n\t;\nEND;<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"Alter-Existing-Table\">Alter Existing Table<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Need to alter  the  existing table <\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">-- Add time stamp columns\n-- by default make the columns hidden\nALTER TABLE temp.booking \n\tADD EffectiveFrom DateTime2 GENERATED ALWAYS AS ROW START HIDDEN\n\t\tCONSTRAINT DF_Booking_EffectiveFrom DEFAULT SYSUTCDATETIME()\n\t, EffectiveTo DateTime2 GENERATED ALWAYS AS ROW END HIDDEN\n\t\tCONSTRAINT DF_Booking_EffectiveTo DEFAULT CONVERT(DateTime2, '9999-12-31 23:59:59.9999999')\n\t, PERIOD FOR SYSTEM_TIME (EffectiveFrom, EffectiveTo)\n-- Add the primary key\nALTER TABLE temp.booking\nADD CONSTRAINT PK_Booking PRIMARY KEY (booking_id);\n-- Turn on system versioning\nALTER TABLE temp.booking SET (SYSTEM_VERSIONING=ON (HISTORY_TABLE=history.booking));\n<\/pre><\/div>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"Populate-Table\">Populate Table<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Data comes from AdventureWorks database. Feel free to substitute your own.<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">INSERT\tINTO\nDBO.HomeAddress\n(\n\tAddressID\n\t, AddressLine1\n\t, City\n\t, ProvinceCode\n\t, ProvinceName\n\t, CountryName\n\t, PostalCode\n)\nSELECT\t\t\tTOP (5)\n\t\t\t\taddr.AddressID\n\t\t\t\t, addr.AddressLine1\n\t\t\t\t, addr.City\n\t\t\t\t, StateProvince.StateProvinceCode\tProvinceCode\n\t\t\t\t, StateProvince.Name\t\t\t\tProvinceName\n\t\t\t\t, Country.Name\t\t\t\t\t\tCountryName\n\t\t\t\t, addr.PostalCode\nFROM\t\t\tAdventureWorks.person.Address\t\taddr\n\t\t\t\tINNER JOIN AdventureWorks.Person.StateProvince StateProvince\n\t\t\t\tON StateProvince.StateProvinceID = addr.StateProvinceID\n\t\t\t\tINNER JOIN AdventureWorks.Person.CountryRegion\tCountry\n\t\t\t\tON Country.CountryRegionCode = StateProvince.CountryRegionCode\nWHERE\t\t\tCountry.Name\t\t\t\t= 'Canada'\n\t\t\t\tAND StateProvince.Name\t\t= 'Ontario'\n;<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"View-Initial-Data\">View Initial Data<\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">Make sure the data was added correctly<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">SELECT\t\t\tAddressID\n\t\t\t  , AddressLine1\n\t\t\t  , City\n\t\t\t  , ProvinceCode\n\t\t\t  , ProvinceName\n\t\t\t  , CountryName\n\t\t\t  , PostalCode\n\t\t\t  , EffectiveFrom\n\t\t\t  , EffectiveTo\nFROM\t\t\tdbo.HomeAddress\n;\nGO<\/pre><\/div>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"Update-the-Address-Table\">Update the Address Table<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">When updating rows will be inserted into the history.HomeAddress table.<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">UPDATE\tdbo.HomeAddress\nSET\t\tAddressLine1 = '456 Iron Way'\nWHERE\tAddressID = (SELECT MAX(AddressID) FROM dbo.HomeAddress)\n;\nGO\nUPDATE dbo.HomeAddress\nSET\t\tAddressLine1 = '3333 Bouncy Street'\nWHERE\tAddressID = (SELECT MAX(AddressID) FROM dbo.HomeAddress)\n;\nGO<\/pre><\/div>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"View-Updated-Data\">View Updated Data<\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">Select query to show the updated data. The EffectiveTo date has a value of 9999-12-31<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">SELECT\t\t\tAddressID\n\t\t\t  , AddressLine1\n\t\t\t  , City\n\t\t\t  , ProvinceCode\n\t\t\t  , ProvinceName\n\t\t\t  , CountryName\n\t\t\t  , PostalCode\n\t\t\t  , EffectiveFrom\n\t\t\t  , EffectiveTo\nFROM\t\t\tdbo.HomeAddress\n;<\/pre><\/div>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"View-Historical-Data\">View Historical Data<\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">Now show previous values. You can see the date ranges applied to EffectiveFrom and EffectiveTo<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">SELECT\t\t\tAddressID\n\t\t\t  , AddressLine1\n\t\t\t  , City\n\t\t\t  , ProvinceCode\n\t\t\t  , ProvinceName\n\t\t\t  , CountryName\n\t\t\t  , PostalCode\n\t\t\t  , EffectiveFrom\n\t\t\t  , EffectiveTo\nFROM\t\t\thistory.HomeAddress\n;<\/pre><\/div>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"Select-Values-as-of-Specified-Date\">Select Values as of Specified Date<\/h3>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">DECLARE\t\t@get_EffectiveFrom\tAS DateTime2\nSELECT\t\t@get_EffectiveFrom = MIN(EffectiveFrom)\nFROM\t\thistory.HomeAddress\tHomeAddress\nWHERE\t\tHomeAddress.AddressID =  (SELECT MAX(AddressID) FROM dbo.HomeAddress)\nSELECT\t\t@get_EffectiveFrom\t\tget_EffectiveFrom\nSELECT\t\t*\nFROM\t\tdbo.HomeAddress\nFOR\t\t\tSYSTEM_TIME AS OF @get_EffectiveFrom\n;<\/pre><\/div>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"Join-Temporal-Tables\">Join Temporal Tables<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"Create-a-Second-Temporal-Table\">Create a Second Temporal Table<\/h3>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">IF EXISTS (\n\t\t  SELECT\t[TABLE_NAME]\n\t\t  FROM\t\tINFORMATION_SCHEMA.TABLES\n\t\t  WHERE\t\tTABLE_TYPE\t\t   = 'BASE TABLE'\n\t\t\t\t\tAND [TABLE_NAME]   = 'Company'\n\t\t\t\t\tAND [TABLE_SCHEMA] = 'dbo'\n\t\t  )\nBEGIN\n\tALTER TABLE dbo.Company SET (SYSTEM_VERSIONING = OFF);\n\tTRUNCATE TABLE dbo.Company;\n\tALTER TABLE dbo.Company SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = history.CompanyName));\nEND;\nELSE\nBEGIN\n\tCREATE TABLE dbo.Company\n\t(\n\t\tCompanyId\t\tInt\t\t\t NOT NULL PRIMARY KEY CLUSTERED\n\t\t, AddressID\t\tInt\t\t\t NOT NULL\t-- FK\n\t\t, CompanyName\tNVarchar(32) NULL\n\t\t, CompanyOwner\tNVarchar(32) NULL\n\t\t-- can add HIDDEN flag when creating columns\n\t\t, EffectiveFrom DateTime2\t GENERATED ALWAYS AS ROW START NOT NULL\n\t\t, EffectiveTo\tDateTime2\t GENERATED ALWAYS AS ROW END NOT NULL\n\t\t, PERIOD FOR SYSTEM_TIME(EffectiveFrom, EffectiveTo)\n\t)\n\tWITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = history.Company));\n\t;\nEND;<\/pre><\/div>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"Populate-with-Initial-Values\">Populate with Initial Values<\/h3>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">DECLARE\t\t@get_AddressId\tAS Int\nSELECT\t\t@get_AddressId = MAX(HomeAddress.AddressID)\nFROM\t\tdbo.HomeAddress\tHomeAddress\nSELECT\t\t@get_AddressId\tget_AddressId\n--DECLARE\t\t@get_AddressId\tAS Int = 31;\nINSERT INTO dbo.Company\n(\n\tCompanyId\n\t, AddressID\n\t, CompanyName\n\t, CompanyOwner\n)\nVALUES\n\t (100\t\t\t\t\t-- CompanyId - int\n\t  , @get_AddressId\t\t-- AddressID - int\n\t  , 'HoneyStore'\t\t-- CompanyName - nvarchar(32)\n\t  , 'Winnie T.'\t\t\t-- CompanyOwner - nvarchar(32)\n\t)\nGO<\/pre><\/div>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"Update-the-Record\">Update the Record<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Update the record and create some records in the history.Company table<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">UPDATE\t\tdbo.Company\nSET\t\t\tAddressID = (SELECT\tMAX(HomeAddress.AddressID) FROM\tdbo.HomeAddress)\n\t\t\t, CompanyName = 'Bucket Store'\t\t-- CompanyName - nvarchar(32)\n\t\t\t, CompanyOwner = 'Eeyore D.'\t\t\t-- CompanyOwner - nvarchar(32)\nWHERE\t\tCompanyId = 100\n;\nGO\nUPDATE\t\tdbo.Company\nSET\t\t\tAddressID =  (SELECT MAX(HomeAddress.AddressID) FROM\tdbo.HomeAddress)\n\t\t\t, CompanyName = 'Springs Springs'\t\t-- CompanyName - nvarchar(32)\n\t\t\t, CompanyOwner = 'Tigger T.'\t\t\t-- CompanyOwner - nvarchar(32)\nWHERE\t\tCompanyId = 100\n;\ngo<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"View-Current-and-Historical-Records\">View Current and Historical Records<\/h4>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">SELECT\t\t*\nFROM\t\thistory.Company\n;\nSELECT\t\t*\nFROM\t\tdbo.Company\n;\nSELECT\t\t*\nFROM\t\thistory.HomeAddress\n;\nSELECT\t\t*\nFROM\t\tdbo.HomeAddress\n;<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"Modify-Company-History-Records\">Modify Company History Records<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Will need to turn off system versioning and after updating turn the system versioning back on.<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">ALTER TABLE dbo.Company\t\tSET (SYSTEM_VERSIONING = OFF);\nSELECT\t\t*\nFROM\t\thistory.Company\nUPDATE\t\thistory.Company\nSET\t\t\tEffectiveFrom\t= '2022-07-01'\n\t\t\t,EffectiveTo\t= '2022-08-01'\nWHERE\t\tCompanyName\t\t= 'HoneyStore'\n;\nDECLARE\t\t@get_EffectiveTo\tAS DateTime2\nSELECT\t\t@get_EffectiveTo = Company.EffectiveFrom\nFROM\t\tdbo.Company\tCompany\nWHERE\t\tCompany.CompanyId = 100\nUPDATE\t\thistory.Company\nSET\t\t\tEffectiveFrom\t= '2022-08-01'\n\t\t\t,EffectiveTo\t= @get_EffectiveTo\nWHERE\t\tCompanyName\t\t= 'Bucket Store'\n;\nALTER TABLE dbo.Company\tSET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = history.Company));\n<\/pre><\/div>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"View-the-Changes\">View the Changes<\/h4>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">SELECT\t\t*\nFROM\t\thistory.Company\n;\nSELECT\t\t*\nFROM\t\tdbo.Company\n;<\/pre><\/div>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"Modify-the-Home-Address-History-Table\">Modify the Home Address History Table<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Same process as modify the company table.<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">ALTER TABLE dbo.HomeAddress\t\tSET (SYSTEM_VERSIONING = OFF);\nUPDATE\t\thistory.HomeAddress\nSET\t\t\tEffectiveFrom\t= '2022-07-01'\n\t\t\t, EffectiveTo\t= '2022-08-15'\nWHERE\t\tAddressLine1\t= 'Station E'\n;\nUPDATE\t\thistory.HomeAddress\nSET\t\t\tEffectiveFrom\t= '2022-08-15'\nWHERE\t\tAddressLine1\t= '456 Iron Way'\n;\nALTER TABLE dbo.HomeAddress\tSET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = history.HomeAddress));\n<\/pre><\/div>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"View-the-Changes.1\">View the Changes<\/h4>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">SELECT\t\t*\nFROM\t\thistory.HomeAddress;\nSELECT\t\t*\nFROM\t\tdbo.HomeAddress\n;<\/pre><\/div>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"Query-the-Temporal-Tables\">Query the Temporal Tables<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"Modifiers-for-History-Table\">Modifiers for History Table<\/h3>\n\n\n\n<div class=\"wp-block-getwid-table alignwide has-vertical-align-top\"><table><thead><tr><th style=\"text-align:left\"><strong>Expression<\/strong><\/th><th style=\"text-align:left\"><strong>Qualifying Rows<\/strong><\/th><th style=\"text-align:left\"><strong>Note<\/strong><\/th><\/tr><\/thead><tbody><tr><td style=\"vertical-align:bottom\"><\/td><td><\/td><td><\/td><\/tr><tr><td><code>AS OF<\/code> <em>date_time<\/em><\/td><td><code>ValidFrom &lt;=<\/code> <em>date_time<\/em> <code>AND ValidTo &gt;<\/code> <em>date_time<\/em><\/td><td>Returns a table with rows containing the values that were current at the specified point in time in the past. Internally, a union is performed between the temporal table and its history table and the results are filtered to return the values in the row that was valid at the point in time specified by the <em>date_time<\/em> parameter. The value for a row is deemed valid if the <em>system_start_time_column_name<\/em> value is less than or equal to the <em>date_time<\/em> parameter value and the <em>system_end_time_column_name<\/em> value is greater than the <em>date_time<\/em> parameter value.<\/td><\/tr><tr><td><code>FROM<\/code> <em>start_date_time<\/em> <code>TO<\/code> <em>end_date_time<\/em><\/td><td><code>ValidFrom &lt;<\/code> <em>end_date_time<\/em> <code>AND ValidTo &gt;<\/code> <em>start_date_time<\/em><\/td><td>Returns a table with the values for all row versions that were active within the specified time range, regardless of whether they started being active before the <em>start_date_time<\/em> parameter value for the <code>FROM<\/code> argument or ceased being active after the <em>end_date_time<\/em> parameter value for the <code>TO<\/code> argument. Internally, a union is performed between the temporal table and its history table. The results are filtered to return the values for all row versions that were active at any time during the time range specified. Rows that stopped being active exactly on the lower boundary defined by the <code>FROM<\/code> endpoint aren&#8217;t included, and records that became active exactly on the upper boundary defined by the <code>TO<\/code> endpoint are also not included.<\/td><\/tr><tr><td><code>BETWEEN<\/code> <em>start_date_time<\/em> <code>AND<\/code> <em>end_date_time<\/em><\/td><td><code>ValidFrom &lt;=<\/code> <em>end_date_time<\/em> <code>AND ValidTo &gt;<\/code> <em>start_date_time<\/em><\/td><td>Same as previous in the <code>FOR SYSTEM_TIME FROM<\/code> <em>start_date_time<\/em> <code>TO<\/code> <em>end_date_time<\/em> description, except the table of rows returned includes rows that became active on the upper boundary defined by the <em>end_date_time<\/em> endpoint.<\/td><\/tr><tr><td><code>CONTAINED IN<\/code> (<em>start_date_time<\/em>, <em>end_date_time<\/em>)<\/td><td><code>ValidFrom &gt;=<\/code> <em>start_date_time<\/em> <code>AND ValidTo &lt;=<\/code> <em>end_date_time<\/em><\/td><td>Returns a table with the values for all row versions that were opened and closed within the specified time range defined by the two period values for the <code>CONTAINED IN<\/code> argument. Rows that became active exactly on the lower boundary or ceased being active exactly on the upper boundary are included.<\/td><\/tr><tr><td>ALL<\/td><td style=\"vertical-align:top\"><br>All rows<\/td><td>Returns the union of rows that belong to the current and the history table.<\/td><\/tr><\/tbody><\/table><\/div>\n\n\n\n<figure class=\"wp-block-table\"><table><tbody><tr><th><strong>Expression<\/strong><\/th><th><strong>Example<\/strong><\/th><\/tr><tr><td><code>AS OF<\/code> <em>date_time<\/em><\/td><td>SELECT *<br>FROM dbo.Table_Name<br>FOR SYSTEM_TIME AS OF &#8216;2023-01-01&#8217;<\/td><\/tr><tr><td><code>FROM<\/code> <em>start_date_time<\/em> <code>TO<\/code> <em>end_date_time<\/em><\/td><td>SELECT *<br>FROM dbo.Table_Name<br>FOR SYSTEM_TIME FROM &#8216;2023-01-01&#8217; TO &#8216;2023-12-31&#8217;<\/td><\/tr><tr><td><code>BETWEEN<\/code> <em>start_date_time<\/em> <code>AND<\/code> <em>end_date_time<\/em><\/td><td>SELECT *<br>FROM dbo.Table_Name<br>FOR SYSTEM_TIME BETWEEN &#8216;2023-01-01&#8217; AND &#8216;2023-12-31&#8217;<\/td><\/tr><tr><td><code>CONTAINED IN<\/code> (<em>start_date_time<\/em>, <em>end_date_time<\/em>)<\/td><td>SELECT *<br>FROM dbo.Table_Name<br>FOR SYSTEM_TIME CONTAINED IN (&#8216;2023-01-01&#8217;, &#8216;2023-12-31&#8217;)<\/td><\/tr><tr><td><code>ALL<\/code><\/td><td>SELECT *<br>FROM dbo.Table_Name<br>FOR SYSTEM_TIME ALL<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"gcf486f353224\">Examples<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"Example-1\">Example 1<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Expected Result<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">&#8212; 2022-07-15<br>&#8212; HoneyStore \/ Winnie T. \/ Station E<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">DECLARE\t\t@dt_1\tAS DateTime2 = '2022-07-15';\nSELECT\t\tCompany.CompanyId\n\t\t\t, Company.AddressID\n\t\t\t, Company.CompanyName\n\t\t\t, Company.CompanyOwner\n\t\t\t, HomeAddress.AddressLine1\nFROM\t\tdbo.Company\tFOR SYSTEM_TIME AS OF @dt_1\tcompany\n\t\t\tINNER JOIN dbo.HomeAddress FOR SYSTEM_TIME AS OF @dt_1\tHomeAddress\n\t\t\tON Company.AddressID  = HomeAddress.AddressID\n;\ngo<\/pre><\/div>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"Example-2\">Example 2<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Expected Result<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">&#8212; 2022-08-01<br>&#8212; Bucket Store \/ Eeyore D. \/ Station E<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">DECLARE\t\t@dt_1\tAS DateTime2 = '2022-08-01';\nSELECT\t\tCompany.CompanyId\n\t\t\t, Company.AddressID\n\t\t\t, Company.CompanyName\n\t\t\t, Company.CompanyOwner\n\t\t\t, HomeAddress.AddressLine1\nFROM\t\tdbo.Company\tFOR\tSYSTEM_TIME AS OF @dt_1\t\tcompany\n\t\t\tINNER JOIN dbo.HomeAddress  FOR SYSTEM_TIME AS OF @dt_1\tHomeAddress\n\t\t\tON Company.AddressID  = HomeAddress.AddressID\n;\ngo<\/pre><\/div>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"Example-3\">Example 3<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Expected Result<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">&#8212; 2022-08-15<br>&#8212; Bucket Store \/ Eeyore D. \/ 456 Iron Way<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">DECLARE\t\t@dt_1\tAS DateTime2 = ' 2022-08-15';\nSELECT\t\tCompany.CompanyId\n\t\t\t, Company.AddressID\n\t\t\t, Company.CompanyName\n\t\t\t, Company.CompanyOwner\n\t\t\t, HomeAddress.AddressLine1\nFROM\t\tdbo.Company\tFOR\tSYSTEM_TIME AS OF @dt_1\t\tcompany\n\t\t\tINNER JOIN dbo.HomeAddress  FOR SYSTEM_TIME AS OF @dt_1\tHomeAddress\n\t\t\tON Company.AddressID  = HomeAddress.AddressID\n;\ngo<\/pre><\/div>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"Remove-Temporal-Tables\">Remove Temporal Tables<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Turn System Versioning off, which will then allow you to drop the tables.<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">ALTER TABLE dbo.Company\t\t\tSET (SYSTEM_VERSIONING = OFF);\nDROP TABLE dbo.Company;\nDROP TABLE history.Company;\n\n\nALTER TABLE dbo.HomeAddress\t\tSET (SYSTEM_VERSIONING = OFF);\nDROP TABLE dbo.HomeAddress;\nDROP TABLE history.HomeAddress;\n<\/pre><\/div>\n","protected":false},"excerpt":{"rendered":"<p>Where we are going, we need to keep track of the history in the tables. How to set up and use Temporal Tables in Microsoft SQL Microsoft SQL has native support for keeping track of Insert \/ Update \/ Delete from system versioned tables. The following explains how to set up and use temporal tables [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_crdt_document":"","footnotes":""},"categories":[43,4,6],"tags":[14,15,30],"series":[],"class_list":["post-685","post","type-post","status-publish","format-standard","hentry","category-sql-t-sql","category-code","category-sql","tag-sql","tag-t-sql","tag-code"],"_links":{"self":[{"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/posts\/685","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/comments?post=685"}],"version-history":[{"count":5,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/posts\/685\/revisions"}],"predecessor-version":[{"id":690,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/posts\/685\/revisions\/690"}],"wp:attachment":[{"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/media?parent=685"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/categories?post=685"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/tags?post=685"},{"taxonomy":"series","embeddable":true,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/series?post=685"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}