30 June 2015

4016 views

2 0

Temporal Tables – Part 2 – Changing history

After my post yesterday, I had planned to take a break for Independence Day holiday, but a commenter asked a question that piqued my interest. How might you edit history? Well, the answer is simple, but the process isn’t exactly simple. Basically, you have to turn off system versioning, update history, and reapply system versioning. Not difficult, but not something you can easily do just by editing a row in history as the temporal structures protect themselves.

select @@version
go
Microsoft SQL Server 2016 (CTP2.0) – 13.0.200.172 (X64) May 21 2015 10:53:07 Copyright (c) Microsoft Corporation Enterprise Evaluation Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

First warning, this is pre-release code, and is subject to change. I will try to make sure it works sooner or later with RTM, probably as I am doing my edits for the book.

To see the metadata, let me create a table that I will not turn on system versioning:

create table notTemporal
(
notTemporalId int primary key
)

Now, sys.tables has a few new columns for temporal, including temporal_type_desc and history_table_id which I will use to list the tables and their temporal usage (filtering out history tables).

select CONCAT(schemas.name,’.’,tables.name) as table_name,
       historyTableSchema.name + ‘.’ + historyTable.name as history_table_name,
       tables.temporal_type_desc
from   sys.tables
         join sys.schemas
            on tables.schema_id = schemas.schema_id
         left outer join sys.tables as historyTable
            join sys.schemas as historyTableSchema
                on historyTable.schema_id = historyTableSchema.schema_id
            on historyTable.object_id = tables.history_table_id
where tables.temporal_type_desc <> ‘HISTORY_TABLE’
go

table_name           history_table_name                          temporal_type_desc
——————– ——————————————- ——————————————-
dbo.company          dbo.MSSQL_TemporalHistoryFor_565577053      SYSTEM_VERSIONED_TEMPORAL_TABLE
dbo.notTemporal      NULL                                        NON_TEMPORAL_TABLE

The dbo.company table is the one from part 1, and you can see its history table. We will see that this contains all of the historical changes, and not the current row, still with a gap between the top two rows because I deleted the row temporarily.

select *
from dbo.MSSQL_TemporalHistoryFor_565577053

This returns:

companyId   name                           companyNumber SysStartTime                SysEndTime
———– —————————— ————- ————————— —————————
1           Company1                       00001         2015-06-30 00:15:01.6419789 2015-06-30 00:16:19.1614451
1           Company Name 1                 00001         2015-06-30 00:16:19.1614451 2015-06-30 00:16:48.2444199
1           Company Name 2                 00001         2015-06-30 00:16:48.2444199 2015-06-30 00:17:23.9396846
1           Company Name 2                 00001         2015-06-30 00:17:23.9396846 2015-06-30 00:33:39.0978045

And the current row is in the primary table:

select *
from dbo.company

Ok, the next stuff I am going to try is to see what happens if you monkey with the data in this table as is, here in CTP 2.0. Use cases might be to remove bad rows, errored data, etc. But mischief is also kind of fun when you come upon a new technology too.)

update dbo.company
set SysEndTime = ‘2015-06-30 00:37:08’

Cool, it protects this data

Msg 13537, Level 16, State 1, Line 55
Cannot update SYSTEM_TIME PERIOD columns in table ‘testTemporal.dbo.company’.

What about the SysStartTime?

update dbo.company
set SysStartTime = ‘2015-06-30 00:37:08’

Same deal

Msg 13537, Level 16, State 1, Line 62
Cannot update SYSTEM_TIME PERIOD columns in table ‘testTemporal.dbo.company’.

Not allowed either. And modifying the history table directly is also not allowed. Here I want to make the row appear to have existed since the 15th of June, rather than the 30th:

update dbo.MSSQL_TemporalHistoryFor_565577053
set SysStartTime = ‘2015-06-15’
where companyId = 1
and sysEndTime = ‘2015-06-30 00:16:19.1614451’

Wah, wah, wah….

Msg 13561, Level 16, State 1, Line 70
Cannot update rows in a temporal history table ‘testTemporal.dbo.MSSQL_TemporalHistoryFor_565577053’.

However, if you want to make a change to the data, or load in old history data, you can do this by turning off versioning, building the table as it is needed, and then turning versioning back on, making sure to specify the table to use.

alter table dbo.company
set (SYSTEM_VERSIONING = OFF);

Now you can run your update query to set the start time back to June 15, so this row existed longer in history than it really did.

update dbo.MSSQL_TemporalHistoryFor_565577053
set SysStartTime = ‘2015-06-15’
where companyId = 1
and sysEndTime = ‘2015-06-30 00:16:19.1614451’

Succeeds. Now, we turn back on system versioning, being sure to specify the history table, or you will get another new table (second repeat, but it is important or you will have a lot of these tables around). If the table is large, you may not want to do the consistency check, but I generally would myself because structures like this can be tricky to get right, so it will be safer to let it check the structure.

ALTER TABLE dbo.Company
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE=dbo.MSSQL_TemporalHistoryFor_565577053, DATA_CONSISTENCY_CHECK = ON));

Now looking at the entire history, you see the row started at 6-15, not 6-30.

select *
from company FOR SYSTEM_TIME CONTAINED IN (‘1900-01-01′,’9999-12-31 23:59:59.9999999’)
order by SysStartTime desc

Which returns:

companyId   name                           companyNumber SysStartTime                SysEndTime
———– —————————— ————- ————————— —————————
1           Company1                       00001         2015-06-30 00:37:07.1375063 9999-12-31 23:59:59.9999999
1           Company Name 2                 00001         2015-06-30 00:17:23.9396846 2015-06-30 00:33:39.0978045
1           Company Name 2                 00001         2015-06-30 00:16:48.2444199 2015-06-30 00:17:23.9396846
1           Company Name 1                 00001         2015-06-30 00:16:19.1614451 2015-06-30 00:16:48.2444199
1           Company1                       00001         2015-06-15 00:00:00.0000000 2015-06-30 00:16:19.1614451

Now you want to see the row as of 6-15:

select priorCompany.*
from company FOR SYSTEM_TIME AS OF ‘2015-06-15’ as priorCompany

It is there!

companyId name companyNumber SysStartTime SysEndTime
———– —————————— ————- ————————— —————————
1 Company1 00001 2015-06-15 00:00:00.0000000 2015-06-30 00:16:19.1614451

Still not on 6-14:

select priorCompany.*
from company FOR SYSTEM_TIME AS OF ‘2015-06-14’ as priorCompany

Update: Security Concurrency

A commenter asked about how this works with concurrency, and I also wanted to note about security. Generally speaking, this is definitely not something that just anyone should do. The security of turning off system versioning will require the use to have rights to alter the table’s structure. Not general purpose rights to be sure, though you could wrap the functionality in a stored procedure if it were desired…

For concurrency, a concern is that while you have system versioning turned off, you will not be gathering history. A way to prevent other connections from messing with the table while you are altering history is to use a transaction. Start the transaction, and turn off system versioning:

BEGIN TRANSACTION

alter table dbo.company
set (SYSTEM_VERSIONING = OFF);

Now go off to another connection and try to execute INSERT INTO dbo.company (name, companyNumber) VALUES (‘Company 2′,’00002’) and on another, SELECT * FROM dbo.company (NOLOCK)
You will be blocked on both connections due to the exclusive schema lock from the table alter, yes, even NOLOCK has to wait for a schema lock. So you can make the history change, safely, while everyone else waits patiently.

update dbo.MSSQL_TemporalHistoryFor_565577053
set SysStartTime = ‘2015-06-14’
where companyId = 1
and sysStartTime = ‘2015-06-15 00:00:00.0000000’

Now, turn back on system versioning:

ALTER TABLE dbo.Company
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE=dbo.MSSQL_TemporalHistoryFor_565577053, DATA_CONSISTENCY_CHECK = ON));

Now check the history structure:

select *
from company FOR SYSTEM_TIME CONTAINED IN (‘1900-01-01′,’9999-12-31 23:59:59.9999999’)
order by SysStartTime desc

Now commit the transaction on the other connection and run the statement, again:

COMMIT TRANSACTION
GO
select *
from company FOR SYSTEM_TIME CONTAINED IN (‘1900-01-01′,’9999-12-31 23:59:59.9999999’)
order by CompanyNumber, SysStartTime desc
go

You can see that the new row was added from your other connection:

Clearly not something you want to do in a highly concurrent system too often, but it will be data safe in any case.

Very cool stuff… More to come… Standard caveat: I won’t be doing any guessing in these blogs because if I guess right and NDA information is out there that I missed, I could get in trouble accidentally. But I will try to make sure if there are big changes in syntax that I note it, or redo the blog syntax so I don’t end up with bad information out there.

Temporal Tables – Part 2 – Changing history

Subscribe for more articles

Rate this article

Louis Davidson

Related articles

4 Reasons to use KustoDB for Real-Time