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.
Load comments