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 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

companyId   name                           companyNumber SysStartTime                SysEndTime
----------- ------------------------------ ------------- --------------------------- ---------------------------
1           Company1                       00001         2015-06-30 00:37:07.1375063 9999-12-31 23:59:59.9999999

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

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-14 00:00:00.0000000 2015-06-30 00:16:19.1614451

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:


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-14 00:00:00.0000000 2015-06-30 00:16:19.1614451
2           Company 2                      00002         2015-07-01 22:42:10.3875967 9999-12-31 23:59:59.9999999

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.

Published Tuesday, June 30, 2015 7:07 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

 

Marcel said:

Thanks Louis, exactly the kind of ability I was thinking about when I queried your last post.  Resolving an invalid timeline is pretty important.

June 30, 2015 8:01 PM
 

Dan Holmes said:

Though this seems like you can make a change like this i was wondering since this is DDL, if history would be off for all user or just the current session?  I added a WAITFOR DELAY between the UPDATE and SET (SYSTEM_VERSIONING = ON)...

Then in another window inserted a new row and updated it.

insert into dbo.company (name, companyNumber)

values ('Asdf5','00005')

update company

set    name += name

where  companyId = 5

Looking back at the table now you only get one row so indeed this method works but it is a database wide setting.  In fact i can't get the history on any row i add to the table after versioning was turned off even though i have executed the statement to turn it back on.

July 1, 2015 8:34 AM
 

drsql said:

@Marcel Thanks!

@Dan good point, and I have just added to the blog to cover this. If you do the ALTER in a transaction, you will e able to safely change the structure without any worry with other connections being able to change the data (or even view the data for that matter!)

July 1, 2015 7:33 PM
 

Kevin Fries said:

Here's something that "came across my desk" which may be of interest.

http://blogs.lessthandot.com/index.php/uncategorized/how-to-drop-a-temporal-table/

How to drop a Temporal Table

July 4, 2015 8:23 PM
 

drsql said:

@Kevin Yeah, I haven't even started to consider that task just yet. Hopefully we get a better story eventually, but if not, I expect tools like Red Gate SQL Compare will take care of the heavy lifting of changing the temporal table (and setting up the saving of the history data as well.)

July 8, 2015 8:38 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Links to my other sites

Archives

Privacy Statement