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:
|
1 2 |
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:
|
1 2 3 4 5 6 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
--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’.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
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’:
|
1 2 3 4 5 6 |
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’.
|
1 2 |
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:
|
1 2 3 |
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’:
|
1 2 |
SELECT * FROM Sales.SalesOrder FOR SYSTEM_TIME AS OF '2016-05-02 02:53:10' |
This returns:
|
1 2 3 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 |
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:
|
1 2 3 4 5 6 |
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:
|
1 2 |
SELECT * FROM Sales.SalesOrder FOR SYSTEM_TIME CONTAINED IN ('1900-01-01','9999-12-31 23:59:59.9999999'); |
Or
|
1 2 |
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.
Load comments