THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

John Paul Cook

SQL Server 2016 Temporal Queries Sample Code

Temporal tables in SQL Server 2016 and Azure SQL Database allow you to have historical changes to data maintained for you automatically. You don’t have to write custom code to maintain historical values. New SQL syntax allows you to query and retrieve the data as it was in the past or you can simply retrieve the most recent value. The official documentation is found at https://msdn.microsoft.com/en-us/library/dn935015.aspx and I encourage you to take advantage of it.

The code posted below will help you understand the nuances and subtleties of temporal SELECT statements. I created a simple table structure for you to practice with. What you need to focus on is understanding the boundary conditions such as when a query is inclusive of a datetime boundary or not. You also need to understand what date values would cause overlaps or gaps. Figure out how the various temporal expressions work with datetime boundary conditions.

Copy the code below and paste it into SQL Server Management Studio connected to SQL Server 2016. Make sure you are connected to a user database instead of a system database. Pay attention to the comments in the code.

-- It is recommended that you execute all of the code in each section,
-- each section in order, one section at a time. Understand each
-- section before proceeding to the next one. Pay attention to boundary
-- conditions. It's important to understand what is included and what
-- isn't.

-- The approach taken in sections 1 is definitely not a pattern
-- production code. The purpose of these examples is to teach you how
-- to run temporal SELECT statements. How the data was faked and inserted
-- does not represent best practices for production code. The code in
-- section 1 sets things up for you to learn how to query in sections 2-7.
-- Refer to the official documentation for examples of how to create
-- temporal tables.

---------- BEGIN SECTION 1 ----------

-- Several tricks are used to load explicit data values. Do not
-- use this section as an example for production code. This section
-- was done the way it was for the sole purpose of providing data for
-- the subsequent sections.

-- create the simplest possible table for temporal queries
CREATE TABLE dbo.alphabet
(
     letter   CHAR(1)   NOT NULL PRIMARY KEY CLUSTERED  -- must have a primary key
    ,sysstart DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
    ,sysend   DATETIME2 NOT NULL DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999')
);
GO

INSERT INTO dbo.alphabet
(letter, sysstart)
VALUES
('X', '1-JAN-2016');
GO

ALTER TABLE dbo.alphabet ADD PERIOD FOR SYSTEM_TIME (sysstart, sysend);

CREATE TABLE dbo.alphabet_history
(
     letter   CHAR(1)      NOT NULL
    ,sysstart DATETIME2 NOT NULL
    ,sysend   DATETIME2 NOT NULL
);
GO

INSERT INTO dbo.alphabet_history
(letter, sysstart, sysend)
VALUES
('A', '2015-01-01', '2015-02-01')  -- the boundary condiions are determind by the temporal expressions
,('B', '2015-02-01', '2015-03-01')
,('C', '2015-03-01', '2015-04-01')
,('D', '2015-04-01', '2015-05-01')
,('E', '2015-05-01', '2015-05-31')  -- gap between May 31 and June 1
,('F', '2015-06-01', '2015-06-30')  -- another gap, what happens at June 30 00:00:00?
,('G', '2015-07-01', '2015-08-01')
,('H', '2015-08-01', '2015-09-01')
,('I', '2015-09-01', '2015-10-01')
,('J', '2015-10-01', '2015-11-01')
,('K', '2015-11-01', '2015-12-01')
,('L', '2015-12-01', '2016-01-01')
;
GO

ALTER TABLE dbo.alphabet
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.alphabet_history, DATA_CONSISTENCY_CHECK = ON))
GO

-- look at the contents of the tables
SELECT * FROM dbo.alphabet;  -- 1 row
SELECT * FROM dbo.alphabet_history;  -- 12 rows
---------- END SECTION 1 ----------

-- run temporal queries

---------- BEGIN SECTION 2 ----------

-- use the ALL expression

SELECT * FROM dbo.alphabet  -- 13 rows
FOR SYSTEM_TIME ALL;

SELECT * FROM dbo.alphabet  -- 13 rows
FOR SYSTEM_TIME ALL
ORDER BY sysstart;

SELECT * FROM dbo.alphabet  -- 13 rows
FOR SYSTEM_TIME ALL
ORDER BY sysend;

---------- END SECTION 2 ----------

---------- BEGIN SECTION 3 ----------

-- AS OF datetime
--    means
--    datetime value >= starting datetime
--    and
--    datetime value < ending datetime

DECLARE @tempus DATETIME2 = CAST('2015-01-15' AS DATETIME2);
SELECT *,'2015-01-15' FROM dbo.alphabet  -- data is actually in alphabet_history
FOR SYSTEM_TIME AS OF @tempus;

SET @tempus = CAST('2015-01-31' AS DATETIME2);
SELECT *,'2015-01-31' FROM dbo.alphabet  -- data is actually in alphabet_history
FOR SYSTEM_TIME AS OF @tempus;

SET @tempus = CAST('2015-01-31 12:34:56' AS DATETIME2);
SELECT *,'2015-01-31 12:34:56' FROM dbo.alphabet  -- data is actually in alphabet_history
FOR SYSTEM_TIME AS OF @tempus;

SET @tempus = CAST('2015-02-01' AS DATETIME2);
SELECT *,'2015-02-01' FROM dbo.alphabet  -- data is actually in alphabet_history
FOR SYSTEM_TIME AS OF @tempus;

SET @tempus = CAST('2015-02-01' AS DATETIME2);
SELECT *,'2015-02-01' FROM dbo.alphabet  -- data is actually in alphabet_history
FOR SYSTEM_TIME AS OF @tempus;

SET @tempus = CAST('2015-02-01' AS DATETIME2);
SELECT *,'2015-02-01' FROM dbo.alphabet  -- data is actually in alphabet_history
FOR SYSTEM_TIME AS OF @tempus;

SET @tempus = CAST('2015-05-31' AS DATETIME2);  -- this day is undefined when using AS OF
SELECT *,'2015-05-31' FROM dbo.alphabet  -- no data because the 2015-05-31 isn't < end datetime
FOR SYSTEM_TIME AS OF @tempus;

SET @tempus = CAST('2015-05-31 12:34:56' AS DATETIME2);  -- also undefined when using AS OF
SELECT *,'2015-05-31 12:34:56' FROM dbo.alphabet  -- no data because it isn't < end datetime
FOR SYSTEM_TIME AS OF @tempus;

SET @tempus = CAST('2015-06-01' AS DATETIME2);
SELECT *,'2015-06-01' FROM dbo.alphabet  -- works because 2015-06-01 is >= start datetime
FOR SYSTEM_TIME AS OF @tempus;

---------- END  SECTION 3 ----------

---------- BEGIN SECTION 4 ----------

-- use BETWEEN expression

DECLARE @startBetween DATETIME2 = CAST('2015-01-01' AS DATETIME2);
DECLARE @endBetween   DATETIME2 = CAST('2015-01-31' AS DATETIME2);
SELECT * FROM dbo.alphabet
FOR SYSTEM_TIME BETWEEN @startBetween AND @endBetween;

SET @endBetween = CAST('2015-01-31 12:34:56' AS DATETIME2)
SELECT * FROM dbo.alphabet
FOR SYSTEM_TIME BETWEEN @startBetween AND @endBetween;

SET @endBetween = CAST('2015-02-01' AS DATETIME2)
SELECT * FROM dbo.alphabet
FOR SYSTEM_TIME BETWEEN @startBetween AND @endBetween;

SET @startBetween = CAST('2015-01-31' AS DATETIME2)
SET @endBetween   = CAST('2015-02-01' AS DATETIME2)
SELECT * FROM dbo.alphabet
FOR SYSTEM_TIME BETWEEN @startBetween and @endBetween;

SET @startBetween = CAST('2015-06-30' AS DATETIME2)
SET @endBetween   = CAST('2015-07-01' AS DATETIME2)
SELECT * FROM dbo.alphabet
FOR SYSTEM_TIME BETWEEN @startBetween AND @endBetween;

SET @startBetween = CAST('2015-04-15' AS DATETIME2)
SET @endBetween   = CAST('2015-09-15' AS DATETIME2)
SELECT * FROM dbo.alphabet
FOR SYSTEM_TIME BETWEEN @startBetween AND @endBetween;

---------- END SECTION 4 ----------

---------- BEGIN SECTION 5 ----------

-- use FROM expression

DECLARE @startFrom DATETIME2 = CAST('2015-01-01' AS DATETIME2);
DECLARE @endFrom   DATETIME2 = CAST('2015-01-31' AS DATETIME2);
SELECT * FROM dbo.alphabet
FOR SYSTEM_TIME FROM @startFrom TO @endFrom;

SET @endFrom = CAST('2015-01-31 12:34:56' AS DATETIME2)
SELECT * FROM dbo.alphabet
FOR SYSTEM_TIME FROM @startFrom TO @endFrom;

SET @endFrom = CAST('2015-02-01' AS DATETIME2)
SELECT * FROM dbo.alphabet
FOR SYSTEM_TIME FROM @startFrom TO @endFrom;  -- only returns one row

SET @startFrom = CAST('2015-01-31' AS DATETIME2)
SET @endFrom   = CAST('2015-02-01' AS DATETIME2)
SELECT * FROM dbo.alphabet
FOR SYSTEM_TIME FROM @startFrom TO @endFrom;

SET @startFrom = CAST('2015-06-30' AS DATETIME2)
SET @endFrom   = CAST('2015-07-01' AS DATETIME2)
SELECT * FROM dbo.alphabet
FOR SYSTEM_TIME FROM @startFrom TO @endFrom;  -- no rows because of the gap

SET @startFrom = CAST('2015-06-30' AS DATETIME2)
SET @endFrom   = CAST('2015-07-01' AS DATETIME2)
SELECT * FROM dbo.alphabet
FOR SYSTEM_TIME FROM @startFrom TO @endFrom;  -- no rows because of the gap

SET @startFrom = CAST('2015-04-15' AS DATETIME2)
SET @endFrom   = CAST('2015-09-15' AS DATETIME2)
SELECT * FROM dbo.alphabet
FOR SYSTEM_TIME FROM @startFrom TO @endFrom;  -- no rows because of the gap

---------- END SECTION 5 ----------

---------- BEGIN SECTION 6 ----------

-- use CONTAINED IN expression

DECLARE @startContained DATETIME2 = CAST('2015-01-01' AS DATETIME2);
DECLARE @endContained   DATETIME2 = CAST('2015-01-31' AS DATETIME2);
SELECT * FROM dbo.alphabet
FOR SYSTEM_TIME CONTAINED IN (@startContained, @endContained);  -- no rows

SET @startContained = CAST('2014-12-31' AS DATETIME2)
SET @endContained   = CAST('2015-02-01' AS DATETIME2)
SELECT * FROM dbo.alphabet
FOR SYSTEM_TIME CONTAINED IN (@startContained, @endContained);

SET @endContained   = CAST('2015-03-01' AS DATETIME2)
SELECT * FROM dbo.alphabet
FOR SYSTEM_TIME CONTAINED IN (@startContained, @endContained);

---------- END SECTION 6 ----------

---------- BEGIN SECTION 7 ----------

-- Even though the sysstart and sysend columns aren't referenced
-- in the UPDATE, they are automatically updated. Also notice
-- that the row containing the letter X is deleted from the
-- alphabet table and inserted into the alphabet_history table.

UPDATE dbo.alphabet
SET letter = 'Y';

SELECT * FROM dbo.alphabet;

SELECT * FROM dbo.alphabet
FOR SYSTEM_TIME ALL;

---------- END SECTION 7 ----------

-- can't drop a table that is participating in temporal SYSTEM_VERSIONING
-- turn off SYSTEM_VERSIONING to allow DROP TABLE to work
ALTER TABLE dbo.alphabet SET (SYSTEM_VERSIONING = OFF);
DROP TABLE dbo.alphabet;          -- tables are no longer associated and
DROP TABLE dbo.alphabet_history;  -- can be dropped in either order

-- it is possible to create the main table and the history table
-- with a single CREATE TABLE statement.
-- Notice that this CREATE TABLE also contains GENERATED ALWAYS which
-- prevents the insertion of explicit values.
-- After creating a table using this syntax, refresh SSMS and expand
-- the table in the Object Explorer. You will see that the history table
-- was automatically created for you.

CREATE TABLE dbo.alphabet
(
     letter   CHAR(1)   NOT NULL PRIMARY KEY CLUSTERED  -- must have a primary key
    ,sysstart DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL DEFAULT SYSUTCDATETIME()
    ,sysend   DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999')
    ,PERIOD FOR SYSTEM_TIME (sysstart, sysend)
)
WITH (SYSTEM_VERSIONING = ON)
;
GO

Published Thursday, March 31, 2016 2:13 AM by John Paul Cook

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

About John Paul Cook

John Paul Cook is a database and Azure specialist who works in Houston. He previously worked as a Data Platform Solution Architect in Microsoft's Houston office. Prior to joining Microsoft, he was a SQL Server MVP. He is experienced in SQL Server and Oracle database application design, development, and implementation. He has spoken at many conferences including Microsoft TechEd and the SQL PASS Summit. He has worked in oil and gas, financial, manufacturing, and healthcare industries. John is also a Registered Nurse who graduated from Vanderbilt University with a Master of Science in Nursing Informatics and is an active member of the Sigma Theta Tau nursing honor society. He volunteers as a nurse at a clinic that treats low income and uninsured patients. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2. Opinions expressed in John's blog are strictly his own and do not represent Microsoft in any way.

This Blog

Syndication

Privacy Statement