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 3 - Syncing Multiple Modifications

Back last June, I started this series on temporal tables, and in my head, I had enough information to make an educated set of examples on how to use them. In the back of my mind though, I knew that I hadn't quite thought enough about the whole process, particularly when you have several rows (or as I will note in the next blog entry, tables) that you are going to work with as a unit.

So in this blog, I want to look a the mechanics of how multiple operations in the same transaction behave when we are in a single table. Then in the next entry to the series, I will take it to the logical conclusion of how we manage things when we have an "object" (like a salesOrder and salesOrderLineItem) that need to be bundled together.

This is executing in RC3:

select @@version

--------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2016 (RC3) - 13.0.1400.361 (X64)
    Apr  9 2016 01:59:22
    Copyright (c) Microsoft Corporation
    Enterprise Evaluation Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)

First, create a table. Note that you don't have to use the name SysStartTime or SysEndTime for the time period columns, which is the typical name because they use is BOL, but they used a different name in the introductory example. So I used a name more like my personal naming standards. I also noticed that the datetime2 column was not a datetime2(7, but rather was a (2). Later in this series, I will try out the different granularities to show what meaning they have for our queries as well.

CREATE SCHEMA Sales
GO

CREATE TABLE Sales.SalesOrder

    SalesOrderId int NOT NULL CONSTRAINT PKSalesOrder PRIMARY KEY,
    Data varchar(30) NOT NULL,    --just the piece of data I will be changing
    ValidStartTime datetime2 (7) GENERATED ALWAYS AS ROW START,
    ValidEndTime datetime2 (7) 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.
GO

Scenario 1: Single row, in a transaction create a row, update it a few times

Note: I won't demo rolling back, as it will be have exactly as expected.

First, start a transaction, and insert some data.

BEGIN TRANSACTION
INSERT INTO  Sales.SalesOrder (SalesOrderID, Data)
VALUES (1,'Original');

Looking at the data, we see:
 
SELECT *
FROM    Sales.SalesOrder;
SELECT *
FROM   Sales.SalesOrderHistory;

This returns:

SalesOrderId Data                      ValidStartTime              ValidEndTime
------------ ------------------------- --------------------------- ---------------------------
1            Original                  2016-04-30 22:08:48.1200225 9999-12-31 23:59:59.9999999

SalesOrderId Data                      ValidStartTime              ValidEndTime
------------ ------------------------- --------------------------- ---------------------------

No version history yet, naturally, just the data as we created it. Now, still in the transaction, update the row.

--SELECT @@TRANCOUNT; --Check if you need to make sure!
--WAITFOR DELAY '00:00:01'; -- Use a WAITFOR if you want to test this stuff in a single batch, or the times might always be the same.


UPDATE Sales.SalesOrder
SET    Data = 'First Change'
WHERE  SalesOrderID = 1

Then we check the data:

SELECT *
FROM   Sales.SalesOrder;
SELECT *
FROM   Sales.SalesOrderHistory;

You can see a version has been created, but notice the start and end times are exactly the same:
 

SalesOrderId Data                      ValidStartTime              ValidEndTime
------------ ------------------------- --------------------------- ---------------------------
1            First Change              2016-04-30 22:08:48.1200225 9999-12-31 23:59:59.9999999

SalesOrderId Data                      ValidStartTime              ValidEndTime
------------ ------------------------- --------------------------- ---------------------------
1            Original                  2016-04-30 22:08:48.1200225 2016-04-30 22:08:48.1200225

Also note that the start and end time on the base SalesOrder row has not changed either. Lets update the row again:
 
UPDATE Sales.SalesOrder
SET    Data = 'Second Change'
WHERE  SalesOrderID = 1;

SELECT *
FROM   Sales.SalesOrder;
SELECT *
FROM   Sales.SalesOrderHistory;

Still no changes to the timestamp. But we keep accumulating changes:

SalesOrderId Data                      ValidStartTime              ValidEndTime
------------ ------------------------- --------------------------- ---------------------------
1            Second Change             2016-04-30 22:08:48.1200225 9999-12-31 23:59:59.9999999

SalesOrderId Data                      ValidStartTime              ValidEndTime
------------ ------------------------- --------------------------- ---------------------------
1            Original                  2016-04-30 22:08:48.1200225 2016-04-30 22:08:48.1200225
1            First Change              2016-04-30 22:08:48.1200225 2016-04-30 22:08:48.1200225

Now we have completed what we are wanting, so we commit:

COMMIT

Checking the results, effectively, since the versions never really existed, you cannot see them using the syntax in the FROM clause as you can see using the AS OF time of the start timestamp, which would be the only time that even somewhat looks like it might return the history:

SELECT *
FROM   Sales.SalesOrder FOR SYSTEM_TIME  AS OF '2016-04-30 22:08:48.1200225';

This returns:

SalesOrderId Data                      ValidStartTime              ValidEndTime
------------ ------------------------- --------------------------- ---------------------------
1            Second Change             2016-04-30 22:08:48.1200225 9999-12-31 23:59:59.9999999
         
You just see the data. This is as we expected, knowing something of how this stuff works from Part 1. (Yeah, I had to look it up too). AS OF uses:

SysStartTime >= PassedValue > SysEndTime (where SysStartTime and SysEndTime corresponds to the names you chose)

So the changed rows are never seen unless you query the history table.

Scenario 2: Create two rows slightly apart. Want to make sure the start time is different for the rows. Start a transaction, update them both at different times.

The thing here is that we want to see how you use the fact that the StartTime values are synchronized with the transaction. If you have more than one row that need to be treated as a group, ideally you modify them in a transaction already. If for no other reason than rolling back the previous operations in the last modification fails. Here though, we are starting to think temporally. If you don’t synchronize your timestamps, you are apt to get illogical results at times.  I will show this more in Part 4 when I have two tables with control values that need to match (denormalizations are another typical concern. If you expect column1 to match column2, and they are updated in two statements, the time gap in the temporal progression could give you weird results if you hit it just right.)

First, let’s do this without a transaction

INSERT INTO  Sales.SalesOrder (SalesOrderID, Data)
VALUES (2,'Original');

WAITFOR DELAY '00:00:01' --slowing down time makes showing concurrency problems easier so you can hit the gaps easier

INSERT INTO  Sales.SalesOrder (SalesOrderID, Data)
VALUES (3,'Original');

and then, verify you are in a consistent state that you expect:

SELECT *
FROM   Sales.SalesOrder
WHERE  SalesOrderId IN (2, 3);
SELECT *
FROM   Sales.SalesOrderHistory
WHERE  SalesOrderId IN (2, 3);

This returns:

SalesOrderId Data                           ValidStartTime              ValidEndTime
------------ ------------------------------ --------------------------- ---------------------------
2            Original                       2016-04-30 22:13:19.1927715 9999-12-31 23:59:59.9999999
3            Original                       2016-04-30 22:13:20.2358806 9999-12-31 23:59:59.9999999

SalesOrderId Data                           ValidStartTime              ValidEndTime
------------ ------------------------------ --------------------------- ---------------------------

Note that the start time is bit over a second different. Now start a transaction, update both rows in a way that shows the time they were changed. Wait a moment between updates, either manually, or using waitfor, as I have, but this time use a transaction:

BEGIN TRANSACTION;

UPDATE Sales.SalesOrder
SET    Data = SYSDATETIME()
WHERE  SalesOrderId = 2;

WAITFOR DELAY '00:00:02'

UPDATE Sales.SalesOrder
SET    Data = SYSDATETIME()
WHERE  SalesOrderId = 3;

Now, look at the state of the table as you can see it:

SELECT *
FROM   Sales.SalesOrder
WHERE  SalesOrderId IN (2, 3);
SELECT *
FROM   Sales.SalesOrderHistory
WHERE  SalesOrderId IN (2, 3);

The start times are synchronized now for the two rows, so the view of 2 and 3 will be consistent for this change, if not the insert:

SalesOrderId Data                           ValidStartTime              ValidEndTime
------------ ------------------------------ --------------------------- ---------------------------
2            2016-04-30 17:17:16.0944986    2016-04-30 22:17:16.0944986 9999-12-31 23:59:59.9999999
3            2016-04-30 17:17:18.0999866    2016-04-30 22:17:16.0944986 9999-12-31 23:59:59.9999999

SalesOrderId Data                           ValidStartTime              ValidEndTime
------------ ------------------------------ --------------------------- ---------------------------
2            Original                       2016-04-30 22:13:19.1927715 2016-04-30 22:17:16.0944986
3            Original                       2016-04-30 22:13:20.2358806 2016-04-30 22:17:16.0944986

The start time of row 2 is exactly the time you put into the Data column for row 2 (the first row) for both rows. That would effectively be the time the transaction started.

Add one more row, with the SYSDATETIME() value for the Data column, which will let you see when the row was created:

INSERT INTO  Sales.SalesOrder (SalesOrderID, Data)
VALUES (4, SYSDATETIME());

SELECT *
FROM   Sales.SalesOrder
WHERE  SalesOrderId IN (2, 3, 4);
SELECT *
FROM   Sales.SalesOrderHistory
WHERE  SalesOrderId IN (2, 3, 4);

The new row has the same ValidStartTime value as the other modified rows.

SalesOrderId Data                           ValidStartTime              ValidEndTime
------------ ------------------------------ --------------------------- ---------------------------
2            2016-04-30 17:17:16.0944986    2016-04-30 22:17:16.0944986 9999-12-31 23:59:59.9999999
3            2016-04-30 17:17:18.0999866    2016-04-30 22:17:16.0944986 9999-12-31 23:59:59.9999999
4            2016-04-30 17:18:17.5493927    2016-04-30 22:17:16.0944986 9999-12-31 23:59:59.9999999

SalesOrderId Data                           ValidStartTime              ValidEndTime
------------ ------------------------------ --------------------------- ---------------------------
2            Original                       2016-04-30 22:13:19.1927715 2016-04-30 22:17:16.0944986
3            Original                       2016-04-30 22:13:20.2358806 2016-04-30 22:17:16.0944986

Commit the changes:

COMMIT

This is actually really great, because you can effectively update, modify, and delete all rows at the same time as far as the temporal history will reflect notice. I will use this in the next blog entry to deal with logical object level changes (SalesOrder and SalesOrderLineItems).

Scenario 3: Single row, created, updated a few times, deleted.

To show the final thing that will occur. I will, in a transaction, make a new row, change it and toss it aside. You would think the row never really existed, and you would wrong really.  It would have log entries, it might even have been used to create other data. As such, it will still have history, which could be useful in some scenario I haven't yet figured out! (If you roll back the transaction, it really would have never existed).

BEGIN TRANSACTION

INSERT INTO  Sales.SalesOrder (SalesOrderID, Data)
VALUES (5, 'Original');

UPDATE Sales.SalesOrder
SET    Data = 'First Change'
WHERE  SalesOrderID = 5

UPDATE Sales.SalesOrder
SET    Data = 'Second Change'
WHERE  SalesOrderID = 5;

DELETE Sales.SalesOrder
WHERE SalesOrderId = 5;

COMMIT

Look at the data

SELECT *
FROM   Sales.SalesOrder
WHERE  SalesOrderId = 5;

SalesOrderId Data                           ValidStartTime              ValidEndTime
------------ ------------------------------ --------------------------- ---------------------------

But in the history:

SELECT *
FROM   Sales.SalesOrderHistory
WHERE  SalesOrderId = 5;

SalesOrderId Data                           ValidStartTime              ValidEndTime
------------ ------------------------------ --------------------------- ---------------------------
5            Original                       2016-04-30 22:24:45.5750619 2016-04-30 22:24:45.5750619
5            First Change                   2016-04-30 22:24:45.5750619 2016-04-30 22:24:45.5750619
5            Second Change                  2016-04-30 22:24:45.5750619 2016-04-30 22:24:45.5750619

Everything we did is in the results. But no data for SalesOrderId will show up in any query on the table, temporal or otherwise:

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

Returns nothing.

This is all quite interesting, and I hope that it helps you as a reader someday when you are wanting to try out some of these scenarios. In this blog I wanted to look as some esoteric situations for how temporal would work, leading up to how you might need to think of things when you are modifying logical objects of data, rather than just one independent row at a time.

Published Monday, May 2, 2016 9:57 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