Begin automatic row versioning on an existing table (Temporal Tables part 2)

In part 1 I showed in a video how to create a new, temporal table for row versioning in Sql Server. In this post, I will show how to convert an existing table to a system versioned (temporal) table.

CREATE TABLE dbo.PriceList(
	ProductID INT NOT NULL CONSTRAINT PK_PriceList PRIMARY KEY CLUSTERED,
	ListPrice MONEY NOT NULL
);

INSERT INTO dbo.PriceList (ProductID, ListPrice)
VALUES
	(1,10),
	(2,11),
	(3,12);

We start with this small table, same as we used in part 1. Only this table already contains data. Same principles apply to existing tables, we need to add the two columns for start- and end-dates.

So we could try this.

ALTER TABLE dbo.PriceList ADD 
	RowStart DATETIME2(7) GENERATED ALWAYS AS ROW START,
	RowEnd DATETIME2(7) GENERATED ALWAYS AS ROW END,
	PERIOD FOR SYSTEM_TIME(RowStart, RowEnd);

But that gives us this result

Msg 4901, Level 16, State 1, Line 12
ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column 'RowStart' cannot be added to non-empty table 'PriceList' because it does not satisfy these conditions.

So we can do this different ways. We could add the columns as normal columns, set values to them and then decorate them with “Generated always”. Instead, I’m going to add two default-constraints to the columns. RowStart can be set to whatever you find suitable. Current date and time perhaps? I’m going with 1900-01-01, to indicate the actual start-date is unknown.

ALTER TABLE dbo.PriceList ADD 
	RowStart DATETIME2(7) GENERATED ALWAYS AS ROW START CONSTRAINT DF_PriceList_RowStart DEFAULT '1900-01-01',
	RowEnd DATETIME2(7) GENERATED ALWAYS AS ROW END CONSTRAINT DF_PriceList_RowEnd DEFAULT '9999-12-31 23:59:59.9999999',
	PERIOD FOR SYSTEM_TIME(RowStart, RowEnd);

More important than the value for RowStart is the value for RowEnd. It has to be the max available value for the data-type. Since I’m using datetime2 with a high resolution, I need to set the date with seven decimals (three for milliseconds, three for microseconds and 1 for 100 nanoseconds).

Now I have prepared the table for system versioning. All that’s left to do is to alter the table to make it system versioned.

ALTER TABLE dbo.PriceList SET (SYSTEM_VERSIONING=ON(HISTORY_TABLE=dbo.PriceList_History));

And now, if we want to, we can drop the default constraints. They are kind of redundant, since the columns are decorated with Generated Always.

ALTER TABLE dbo.PriceList DROP CONSTRAINT DF_PriceList_RowStart;
ALTER TABLE dbo.PriceList DROP CONSTRAINT DF_PriceList_RowEnd;

And finally, let’s change values in the main table.

UPDATE dbo.PriceList SET ListPrice = 100 WHERE ProductID = 1;

If we now check the main table and the history table, we can see that the automatic row versioning is indeed in effect.

SELECT * FROM dbo.PriceList WHERE ProductID = 1;
SELECT * FROM dbo.PriceList_History WHERE ProductID = 1;

Done!

I hope you found this post useful. In part 3, we will look at how we can convert existing temporal pattern tables and make them automagically system versioned, temporal tables. That is: Tables where we have data in both a main table and a history table.