SQL Server 2016: the Time Travel with temporal table – Part II

INSERT, UPDATE & DELETE commands

After the introduction and the table creation, in this second part, I present the 3 important commands INSERT, UPDATE & DELETE.
I divide my article into 4 parts to be clearer and easier to read:
Part I – Principle & Creation
Part II – INSERT, UPDATE & DELETE commands
Part III – SELECT command
Part IV – Maintenance & Metadata

SQL Server version/built used for this serie of articles is CTP 2.2/13.0.407.1

INSERT Command

The Insert is like a ‘normal’ insert:

INSERT INTO [Zoo].[dbo].[Animals]([Name],[Genus Species],[Number])
     VALUES('African wild cat','Felis silvestris lybica',10)
GO

I check quickly both tables and I can see that animals has my new row and the History table AnimalsHistory is empty.

Can I insert a row and specified the Start Date?
I test…and the result:

Ooooups! I cannot insert a row with a specific Start Date.
The other way is to add a default value for the Start Date and to insert my line with a constraint:

But the result indicates the correct insert date and time, and not my default value.
I precise the default in the Start Date in my next insert:

And I have NOT my default Start Date…

A fun thing… that I remark is the Time in my Start Time is delayed from 2 hours.  Indeed, the time is set to the UTC Time (documented in the msdn). Don’t forget it when you want be precise to retrieve data in a point in time.

My conclusion for the INSERT is very simple, you can just insert from your actual date and you cannot define your Start Date.
I insert with a script all animals that I wish for my zoo.

But I must update the number when the species arrived….

UPDATE Command

The update command is like a standard update for your line:

You can notice that the update has 2 rows affected…
And in the query plan, you can notice that you have a Update of the table Animals and an Insert in the table AnimalsHistory:

To create an update for all lines, I use the command:

SELECT 'UPDATE [Zoo].[dbo].[Animals] SET Number = '+ CONVERT(varchar(10),CAST(RAND(CHECKSUM(NEWID())) * 50 as INT) + 1)  
+' WHERE Name = '''+ Name + ''' AND  [Genus Species]= ''' + [Genus Species] +''';WAITFOR DELAY ''00:05'';'
 FROM [Zoo].[dbo].[Animals]

The command “CAST(RAND(CHECKSUM(NEWID())) * 50 as INT) + 1” gives a random number from 1 to 50.
I run all update at different time 5 minutes for all rows with a WAITFOR DELAY ’00:05′;
Then queries are like:

UPDATE [Zoo].[dbo].[Animals] SET Number = 21 WHERE Name = 'African wild cat' AND  [Genus Species]= 'Felis silvestris lybica';WAITFOR DELAY '00:05';
UPDATE [Zoo].[dbo].[Animals] SET Number = 28 WHERE Name = 'Alligator' AND  [Genus Species]= 'Alligator mississippiensis';WAITFOR DELAY '00:05';
UPDATE [Zoo].[dbo].[Animals] SET Number = 32 WHERE Name = 'Alligator' AND  [Genus Species]= 'Alligator mississippiensis';WAITFOR DELAY '00:05';

After a long time, I run a SELECT on both tables:

In the table Animals, I have all rows “up to date” and in my history table, I have all old values historicized.

DELETE Command

For the delete, the row is deleted from your table like a standard delete. The value is stored in the History Table with an End Date and Time, when the delete command is running.
In my sample, I have two rows with Alligator and I delete the AnimalID “3”.

If we have a look on the query plan, we can see both operation (delete on the table and insert on the history table):

Like every time, it is easy to delete 😉

CORE MESSAGE

• It is easy to insert DATA in the table, as it is the classical way.
• But, you cannot force the Start Date and Time.
• The Start Date and Time is set to the UTC Time. Don’t forget to add the Offset when you will select data from a point in time.
• The update is like a standard update and the old value is stored in the Historic table.
• Like the update, delete is like a standard delete, and the value is stored in the Historic table with an End Date and Time when the delete was executed.