THE SQL Server Blog Spot on the Web

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

Louis Davidson

Temporal Tables - Part 5 - Start and End Type Precision

In this fifth (and maybe final until at least getting to the concurrency chapter prep) blog about temporal I wanted to briefly cover the precision of the ROW START and END times. You can use any of the datetime2 types for these values. The precision will let you choose how many changes would be seen by the user. Even with 6 digits of precision from datetime2(7), there is no guarantee that every change will be visible to the user via the temporal settings on a FROM clause, but it is generally much more likely than if you are using datetime2(0) as we will see.

In this blog, I will use datetime2(0) to give the least possible precision possible to show what can occur. Note that (not unsurprisingly) you have to use the same precision for both ROW START and END times or you get the following error:

Msg 13513, Level 16, State 1, Line 6
SYSTEM_TIME period columns cannot have different datatype precision.

I will use the same basic structures I have used in previous examples, so if you have created the tables, you will need to drop the table and the history table:

ALTER TABLE Sales.SalesOrder
    SET (SYSTEM_VERSIONING = OFF);
go
DROP TABLE Sales.SalesOrder;
DROP TABLE Sales.SalesOrderHistory;
GO

To generate some data, I will use SEQUENCE object that I will format to put out a hexedecimal value, which I will put into a default constraint so the repeating code will be easier to read.

CREATE SEQUENCE Sales.SalesOrderData_SEQUENCE MINVALUE 1 START WITH 1;
GO
CREATE TABLE Sales.SalesOrder

    SalesOrderId int NOT NULL CONSTRAINT PKSalesOrder PRIMARY KEY,
                              --default to a text hex value, so we can see changes...
    Data varchar(30) NOT NULL DEFAULT (SUBSTRING(CONVERT(varchar(20), cast(NEXT VALUE FOR Sales.SalesOrderData_SEQUENCE as varbinary(5)), 1),3,12)),
    ValidStartTime datetime2 (0) GENERATED ALWAYS AS ROW START,
    ValidEndTime datetime2 (0) GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (ValidStartTime, ValidEndTime)

WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = Sales.SalesOrderHistory)); --Another future thought, put in a different schema? I think not, but, it intrigues me nonetheless.

Next let's create a sequence of history rows, starting with a simple insert, wait a second, then three sets of 5 inserts.

--create a first row
INSERT INTO Sales.SalesOrder(SalesOrderId, Data)
VALUES (1, DEFAULT);

WAITFOR DELAY '00:00:01';
GO
--update the table 5 times
UPDATE  Sales.SalesOrder
SET     Data = DEFAULT
WHERE   SalesOrderId = 1;
GO 5
WAITFOR DELAY '00:00:01';
GO
--update the table 5 more times
UPDATE  Sales.SalesOrder
SET     Data = DEFAULT
WHERE   SalesOrderId = 1;
GO 5
WAITFOR DELAY '00:00:01';
GO
--update the table 5 last times
UPDATE  Sales.SalesOrder
SET     Data = DEFAULT
WHERE   SalesOrderId = 1;
GO 5

Now, checking out the data:

SELECT  *
FROM    Sales.SalesOrder
WHERE   SalesOrderId = 1;
SELECT  *
FROM    Sales.SalesOrderHistory
WHERE   SalesOrderId = 1;

The final state of the row has the Data column = '0000000010', and you can see the complete progression from '0000000001' through '000000000F'.

SalesOrderId Data                           ValidStartTime              ValidEndTime
------------ ------------------------------ --------------------------- ---------------------------
1            0000000010                     2016-05-02 02:53:12         9999-12-31 23:59:59

SalesOrderId Data                           ValidStartTime              ValidEndTime
------------ ------------------------------ --------------------------- ---------------------------
1            0000000001                     2016-05-02 02:53:09         2016-05-02 02:53:10
1            0000000002                     2016-05-02 02:53:10         2016-05-02 02:53:10
1            0000000003                     2016-05-02 02:53:10         2016-05-02 02:53:10
1            0000000004                     2016-05-02 02:53:10         2016-05-02 02:53:10
1            0000000005                     2016-05-02 02:53:10         2016-05-02 02:53:10
1            0000000006                     2016-05-02 02:53:10         2016-05-02 02:53:11
1            0000000007                     2016-05-02 02:53:11         2016-05-02 02:53:11
1            0000000008                     2016-05-02 02:53:11         2016-05-02 02:53:11
1            0000000009                     2016-05-02 02:53:11         2016-05-02 02:53:11
1            000000000A                     2016-05-02 02:53:11         2016-05-02 02:53:11
1            000000000B                     2016-05-02 02:53:11         2016-05-02 02:53:12
1            000000000C                     2016-05-02 02:53:12         2016-05-02 02:53:12
1            000000000D                     2016-05-02 02:53:12         2016-05-02 02:53:12
1            000000000E                     2016-05-02 02:53:12         2016-05-02 02:53:12
1            000000000F                     2016-05-02 02:53:12         2016-05-02 02:53:12

Starting at the first second that was recorded, the first thing you can see is the row where Data = '0000000001':

SELECT *
FROM   Sales.SalesOrder FOR SYSTEM_TIME  AS OF '2016-05-02 02:53:09';

SalesOrderId Data                           ValidStartTime              ValidEndTime
------------ ------------------------------ --------------------------- ---------------------------
1            0000000001                     2016-05-02 02:53:09         2016-05-02 02:53:10


But the next row you will see will not be '0000000002', it will actually be '0000000006'. Using fractional times will be truncated. Such as if we try '2016-05-02 02:53:09.9'.

SELECT *
FROM   Sales.SalesOrder FOR SYSTEM_TIME  AS OF '2016-05-02 02:53:09.9';

This returns the same thing that 2016-05-02 02:53:09 does:

SalesOrderId Data                           ValidStartTime              ValidEndTime
------------ ------------------------------ --------------------------- ---------------------------
1            0000000001                     2016-05-02 02:53:09         2016-05-02 02:53:10

If you use the next second, you will get '0000000006':

SELECT *
FROM   Sales.SalesOrder FOR SYSTEM_TIME  AS OF '2016-05-02 02:53:10'

This returns:

SalesOrderId Data                           ValidStartTime              ValidEndTime
------------ ------------------------------ --------------------------- ---------------------------
1            0000000006                     2016-05-02 02:53:10         2016-05-02 02:53:11

The only way you will see rows '0000000001' - '0000000005' is to query the history table. We can only see rows where ValidStartTime <> ValidEndTime. In the following query I will get all of the rows that you can see in the table using each second:

SELECT *
FROM   Sales.SalesOrder FOR SYSTEM_TIME  AS OF '2016-05-02 02:53:09'
UNION ALL
SELECT *
FROM   Sales.SalesOrder FOR SYSTEM_TIME  AS OF '2016-05-02 02:53:10'
UNION ALL
SELECT *
FROM   Sales.SalesOrder FOR SYSTEM_TIME  AS OF '2016-05-02 02:53:11'
UNION ALL
SELECT *
FROM   Sales.SalesOrder FOR SYSTEM_TIME  AS OF '2016-05-02 02:53:12';

Which returns:

SalesOrderId Data                           ValidStartTime              ValidEndTime
------------ ------------------------------ --------------------------- ---------------------------
1            0000000001                     2016-05-02 02:53:09         2016-05-02 02:53:10
1            0000000006                     2016-05-02 02:53:10         2016-05-02 02:53:11
1            000000000B                     2016-05-02 02:53:11         2016-05-02 02:53:12
1            0000000010                     2016-05-02 02:53:12         9999-12-31 23:59:59

Note that this is the same output you will see if you execute the following query that returns all data:

SELECT *
FROM   Sales.SalesOrder FOR SYSTEM_TIME CONTAINED IN ('1900-01-01','9999-12-31 23:59:59.9999999');

Or

SELECT *
FROM   Sales.SalesOrder FOR SYSTEM_TIME ALL;

So you will definitely want to set your precision to the level that you will have the most likelihood of seeing all changes in your data. Once multiple connections are making simultaneous changes, you wouldn't wnt to lose data that would be interesting to the user.

Published Saturday, May 21, 2016 9:52 PM by drsql

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

This Blog

Syndication

Links to my other sites

Archives

Privacy Statement