Temporal Tables and Hidden Period Columns

In my November 2015 post, An Introduction to Temporal Tables in SQL Server 2016 using a DeLorean, I wrote:

The HIDDEN property is optional and will hide these columns from a standard SELECT statement for backward compatibility with our application and queries. You cannot apply the HIDDEN property to an existing column.

It turns out that this is no longer true. You can apply the HIDDEN property to an existing period column.

Let’s assume you have a temporal table containing two visible period columns, StartDate and EndDate, which you’d like to hide from a typical SELECT statement.

Using an ALTER TABLE ... ALTER COLUMN statement, simply place the ADD HIDDEN syntax after the period column name(s).

ALTER TABLE [dbo].[Account] ALTER COLUMN [StartDate] ADD HIDDEN;
ALTER TABLE [dbo].[Account] ALTER COLUMN [EndDate] ADD HIDDEN;

You can also remove this flag if you wish, using DROP HIDDEN:

ALTER TABLE [dbo].[Account] ALTER COLUMN [StartDate] DROP HIDDEN;
ALTER TABLE [dbo].[Account] ALTER COLUMN [EndDate] DROP HIDDEN;

This is a great improvement to an already fantastic feature of SQL Server 2016. Thanks to Borko Novakovic for this tip.

If you have any more temporal table tricks you want to share, find me on Twitter at @bornsql.

Temporal Tables and History Retention

I’m a huge fan of Temporal Tables in SQL Server 2016. I first wrote about them, in a four-part series in November 2015, before SQL Server was even released. I don’t always get this excited about new features.

However, it has some limitations. As part of this week’s T-SQL Tuesday, hosted by the attractive and humble Brent Ozar, I have discovered a Microsoft Connect item I feel very strongly about.

Adam Machanic, the creator of an indispensable tool, sp_WhoIsActive, has created a Connect item entitled Temporal Tables: Improve History Retention of Dropped Columns.

As my readers know, temporal tables have to have the same schema as their base tables (the number and order of columns, and their respective data types, have to match).

Where this breaks down is when a table structure has changed on the base table. The history table also needs to take those changes into account, which could potentially result in data loss or redundant columns in the base table.

Adam suggests allowing columns which no longer appear in the base table to be retained in the history table and marked as nullable (or hidden), and should only appear when performing a point-in-time query by referring to the column(s) explicitly.

I have voted for this suggestion, and at the time of writing, it has 16 upvotes. I encourage you to add your voice to this suggestion.

If you have any other suggestions, or wish to discuss temporal tables, please contact me on Twitter at @bornsql .

Temporal Tables in Azure SQL Database

In the latest Microsoft Azure newsletter I received last week was this most excellent news:

Azure SQL Database Temporal Tables generally available

Temporal Tables let customers track the full history of data changes in Azure SQL Database without custom coding. Customers can focus data analysis on a specific point in time and use a declarative cleanup policy to control retention of historical data. Designed to improve productivity when customers develop applications, Temporal Tables can help:

– Support data auditing in applications.
– Analyze trends or detect anomalies over time.
– Implement slowly changing dimension patterns.
– Perform fine-grained row repairs in cases of accidental data errors made by humans or applications.

For more information on how to integrate Temporal Tables in an application, please visit the Getting Started with Temporal Tables in Azure SQL Database documentation webpage. To use temporal retention, please visit the Manage temporal history with retention policy documentation webpage.

Long-time readers of my blog will remember my short series about Temporal Tables in SQL Server 2016. Now it’s time to play with them on Azure SQL Database too!

Changes to Table Schema

With Azure SQL Database, just like SQL Server 2016, you can change the table schema without breaking the link to the history table. From the above Getting Started link, it states that you perform standard ALTER TABLE statements, “and Azure SQL Database will appropriately propagate changes to the history table”. It’s good to see feature parity across products like this. Temporal tables even work on Basic-sized databases.

Go forth and play with Temporal Tables. You’ll no longer audit data changes the same way. Get the benefits of Change Data Capture without the need for massive complexity.

Share your CDC horror stories with me on Twitter, at @bornsql.

Temporal Tables – When To Use Them

This is the final part of my Temporal Table series. You can read parts one, two and three here.

Last week I demonstrated how to modify temporal tables in SQL Server 2016.

This week I’m answering a very simple question.

When should you use Temporal Tables?

I see two main reasons for using this feature:

  • You are legally obligated to store an audit trail of changes to your data.
  • You currently implement history tracking through triggers and stored procedures or in application code.

Considerations

Tracking changes to your data is a big deal, and in databases with heavy churn, you can end up needing a massive amount of space to handle your history, especially if you need to retain seven years of data, like some of our customers.

If data storage is a concern, I’m going to recommend SQL Server Enterprise Edition. The Books Online documentation specifically states that the history tables for Temporal Tables are implemented with Page Compression by default, which is an Enterprise Edition feature.

If you cannot afford Enterprise Edition, temporal tables will still work, but page compression will not. You may still have to spend money on additional storage.

Realistically, there’s no hard and fast estimate of how much space this feature will use. You will have to do some testing. Based on that testing, you can decide whether to spend money on additional storage, an Enterprise Edition licence, or both.

Whatever your decision, I hope that you have learned something. SQL Server 2016 is, in my opinion, as significant an update to the product as SQL Server 2005 was.

Modifying Temporal Tables – A Primer

This is part three of the Temporal Tables series. You can read parts one and two here.

Last week I demonstrated how temporal tables in SQL Server 2016 work. If you have implemented a history table—populating it with triggers or stored procedures—it works the same way.

This week, we are going to look at how to modify a temporal table that already has data in the history table.

Warning!

Modifying a temporal table can potentially break the audit trail.

In other words, you will be able to modify historic data. Be cognisant of this.

The safest way to control this is by securing the ALTER TABLE command in your production environment, using GRANT ALTER.

In our Account table example, let’s grant privileges to the Admin role (which must exist on the instance, of course).

-- Only allow a certain role to perform ALTER
GRANT ALTER ON OBJECT::[dbo].[Account] TO [AdminRole];
GO

With that in mind, we can continue.

Modify my temporal table

Let’s say we need to make the [AccountName] column in our example table longer, to support 2000 Unicode characters.

Temporal tables are linked to their history tables in a manner similar to views created with SCHEMABINDING.

This means that no changes can be made to either table without breaking the temporal link first.

To do this, we have to run an ALTER TABLE command with the SET SYSTEM_VERSIONING parameter set to OFF.

[Edit: The syntax changed for the latest CTP 3.3, and the command requires brackets around the SYSTEM_VERSIONING statement.]

-- Disable System Versioning on Account table
ALTER TABLE [dbo].[Account] SET (SYSTEM_VERSIONING = OFF);
GO

Now we can modify our primary table and history table with the same change.

-- Modify our primary and history tables
ALTER TABLE [dbo].[Account]
   ALTER COLUMN [AccountName] NVARCHAR(2000) NOT NULL;
ALTER TABLE [History].[Account]
   ALTER COLUMN [AccountName] NVARCHAR(2000) NOT NULL;
GO

Once we have made our modifications, we simply set the SYSTEM_VERSIONING parameter to ON and link back to our history table.

ALTER TABLE [dbo].[Account]
   SET (
      SYSTEM_VERSIONING = ON (
         HISTORY_TABLE = [History].[Account],
         DATA_CONSISTENCY_CHECK = ON
      )
   );
GO

The DATA_CONSISTENCY_CHECK parameter should be enabled, as recommended by Microsoft, to ensure that the history table follows the correct structure and format as the primary table.

Reminder

Because it’s that easy to make changes to temporal tables, it is just as easy to break the audit chain. Make sure you are not breaking any compliance rules.

If you have to change a temporal table, it may make more sense in your audit policy to create a new history table, active from that change, and retain the old history table untouched.

Next week we will look at when it is appropriate to use temporal tables and some factors to consider around making that choice.

Temporal Tables Under The Covers

(This is a more technical post than last week. If you are not familiar with SQL Server internals, I will not be upset if you skip it. Otherwise, let’s dive in.)

Last week I introduced the SQL Server 2016 feature known as System-Versioned Tables, or Temporal Tables. I explained how to convert an existing table to a temporal table and that it uses a methodology called effective dating.

This week, I’m going to go under the covers to see how they actually work, by reviewing the Transaction Log. How exciting!

As we know, every change to the state of a database is written to the transaction log before it is committed. (This does not necessarily apply to In-Memory OLTP tables, which can have delayed durability.)

This means that we can see all changes made to temporal tables, from their creation (or changing an existing table to a temporal table) to their modification.

I’m going to use some undocumented features in this post because I’m using a development environment, and I won’t be sad if I lose my database. I would not recommend running these commands in a production environment.

My development database is in Simple Recovery, which means that I can clear the Transaction Log with a simple CHECKPOINT command. Everything I see in the log after that will be whatever I ran after the checkpoint.

(Note: if we were using the Full Recovery Model instead, we would need to take a transaction log backup first to ensure that the log clears. This includes an implicit checkpoint.)

USE [BornSQL];
GO

CHECKPOINT;
GO

-- Use the undocumented fn_dblog() function
-- with NULL start and end transaction LSNs
-- to return everything in the log.
SELECT * FROM fn_dblog(NULL, NULL);
GO

All we should see here are three rows:

LOP_BEGIN_CKPT Begins the Checkpoint operation
LOP_XACT_CKPT Writes the Checkpoint information to the boot page of the database and commits the transaction
LOP_END_CKPT Ends the Checkpoint operation

Now we can start playing with our temporal tables to see how they work.

Remember last week when I said that adding the history table is a metadata operation? Let’s take a look and see if I was telling the truth, and what that actually means.

Assume we have our [dbo].[Account] table with a row of data for “Lorraine McFly”, which we now need to convert to a temporal table.

covers1(Click to enlarge)

Recall that there are two stages to converting an existing table. We can review these separately and run a checkpoint in between, for simplicity’s sake.

Adding the Temporal Period Columns

In the first stage, we add the two DATETIME2(7) columns and set the temporal period.

The fn_dblog() output gives us 32 rows in the transaction log (excluding the checkpoint information).

covers2(Click to enlarge)

Jump over to the [AllocUnitName] column and have a look through those values. As the transaction log is showing us, these are metadata operations because they are modifying the system base tables only. No user data is being affected by this operation.

However, an exclusive lock is required on the [dbo].[Account] table to perform the change.

You may want to view the SQL Server 2016 Books Online section on system base tables to follow along.

LOP_BEGIN_XACT and LOP_LOCK_XACT are self-explanatory. Even system base tables need to be locked to add or modify rows. In row 6, we see LOP_MODIFY_ROW on the sys.sysschobjs.clst AllocUnitName. This refers to a clustered index change on an object in the database.

Tables can either be heaps or clustered indexes. With a clustered index, the index is the data, in the form of a b-tree (balanced tree).

B_tree_insertion_example
B Tree insertion example with each iteration.

This is what a HoBT is: Heap or b-tree. Paul Randal and several others can take you into a lot more detail if you are curious, but the important thing to note here is that HoBT generally refers to a table.

So when we see a modification to a clustered index in the transaction log, we can think of it as a table with a clustered index that is being modified, as opposed to a heap, which is a table with no clustered index.

(Note: a clustered index does not have to be on the Primary Key of a table. It is a matter of convenience that a Primary Key is created as a clustered index on SQL Server when using Management Studio. It is not a requirement.)

At rows 12 and 15, seen in the transaction log output above, we see two operations following a LOP_HOBT_DDL event. The second operation (see rows 14 and 17), the LOP_INSERT_ROWS operation against sys.sysseobjvalues.clst, would be where the temporal table START and END period properties are being written.

It is really that simple. All we have seen here is that two columns are added to the metadata, and their properties are assigned to a system base table. There is no magic in the structure. All that magic happens in the database engine code itself. And as we’ll see later in this post, there’s no magic in the code.

(Note: the sys.sysseobjvalues base table does not appear in the documentation. Keep in mind that this is based on CTP 3.0 of the product. It would not be the first time that I’ve found a typo in SQL Server either. This could be a mistake, or an undocumented table. Who knows? If I were to hazard a guess, I would think that the “se” in “sysseobjvalues” stands for “system extensions”.)

From rows 28 onward, seen in the log output above, we see that the indexes on the base tables that were modified need to be rebalanced according to the b-tree algorithm. Pretty cool, eh?

Converting to a Temporal Table

The second stage is creating and linking the history table, and this is the piece that I said was a metadata operation.

covers3(Click to enlarge)

Reviewing the transaction log, we can see about 70 log records in the transaction log output.

Sometimes we might see LOP_EXPUNGE_ROWS and LOP_SET_BITS. Don’t be alarmed. These operations are part of the Ghost Cleanup phase. SQL Server is doing its own things behind the scenes, and we would see that in the log records from time to time.

There are a lot more locks in this step of the temporal table conversion, for obvious reasons. A new table has to be created. A new index has to be created. Indexes on the system base tables may have to be re-balanced.

Through all of this activity, one thing is clear: we would see exactly the same activity when a normal user table is created or modified.

Throughout both steps of this entire process, comprising approximately 100 log records, no data is modified in the user table. The only objects in [AllocUnitName] are system base tables. Converting to a temporal table is indeed a metadata operation. However, SQL Server must take an exclusive lock on the primary table, when adding the two PERIOD columns, and creating the history table.

What about modifying the data?

Where does that leave data modifications, then? Let’s update “Lorraine McFly” back to “Lorraine Baines”.

UPDATE [dbo].[Account]
SET [AccountName] = N'Lorraine Baines',
    [ModifiedDate] = SYSUTCDATETIME()
WHERE [AccountID] = 1;
GO

covers4(Click to enlarge)

We can see 24 log records now. First is the obvious modification to the [dbo].[Account] table at row 5. Notice that this happens right at the top of the sequence, after an implicit transaction is started (row 4).

Now, because the [History].[Account] table was empty, SQL Server only allocated space when it was needed. The next few log records are the PFS (Page Free Space, though it’s a little more complicated than that) and IAM operations (Index Allocation Map, but also a little more complicated than that).

(Note: if you want to read more, Paul Randal goes into depth about GAM, SGAM, PFS and other allocation maps in this post.)

This can be a bit confusing, especially to readers who are not familiar with SQL Server internals. Also, we have a lot of log records relating to operations outside of temporal tables.

To make it easier, I’m going to run a checkpoint to clear the transaction log, then update Lorraine to her full name, “Lorraine Baines McFly”.

UPDATE [dbo].[Account]
SET [AccountName] = N'Lorraine Baines McFly',
    [ModifiedDate] = SYSUTCDATETIME()
WHERE [AccountID] = 1;
GO

Reviewing the log records again, we only have 4 rows, excluding the checkpoint.

covers5(Click to enlarge)

Row 4 and 5 are the same as we saw previously: a transaction is created, and the primary table’s row is changed. And as we would expect, these are followed by a LOP_INSERT_ROWS into the [History].[Account] table, followed by a LOP_COMMIT_XACT to commit the entire transaction.

If we had a stored procedure or update trigger, it would perform exactly the same steps, and we would wrap it in our own transaction.

Prove it!

Instead of taking my word for it, let’s compare this against an update trigger on a table which has not been converted to a temporal table.

Firstly, create our new Account table, with the same structure as the original [dbo].[Account] table that we created last week.

-- Non-temporal table
CREATE TABLE [dbo].[AccountNT] (
	[AccountNTID] INT IDENTITY(1, 1) NOT NULL,
	[AccountNumber] BIGINT NOT NULL,
	[AccountName] NVARCHAR(255) NOT NULL,
	[Address] NVARCHAR(1000) NOT NULL,
	[Telephone] NVARCHAR(20) NOT NULL,
	[CreateDate] DATETIME2(7) NOT NULL,
	[ModifiedDate] DATETIME2(7) NOT NULL,
	CONSTRAINT [PK_AccountNT] PRIMARY KEY CLUSTERED ([AccountNTID] ASC)
	);
GO

ALTER TABLE [dbo].[AccountNT] ADD CONSTRAINT [DF_AccountNT_CreateDate] DEFAULT(SYSUTCDATETIME())
FOR [CreateDate];
GO

ALTER TABLE [dbo].[AccountNT] ADD CONSTRAINT [DF_AccountNT_ModifiedDate] DEFAULT(SYSUTCDATETIME())
FOR [ModifiedDate];
GO

-- Populate the table with our test data
INSERT INTO [dbo].[AccountNT] (
	[AccountNumber],
	[AccountName],
	[Address],
	[Telephone]
)
VALUES (
	5551112233,
	N'Lorraine Baines',
	N'1727 Bushnell Avenue, Hill Valley, CA, 90101',
	N'310-555-1212'
);
GO

Secondly, we manually create a history table with the same structure. Then we add a clustered index on the AccountNTID column. This cannot be unique because an AccountNTID may be logged more than once.

-- Manually-created history table
CREATE TABLE [History].[AccountNT] (
	[AccountNTID] INT IDENTITY(1, 1) NOT NULL,
	[AccountNumber] BIGINT NOT NULL,
	[AccountName] NVARCHAR(255) NOT NULL,
	[Address] NVARCHAR(1000) NOT NULL,
	[Telephone] NVARCHAR(20) NOT NULL,
	[CreateDate] DATETIME2(7) NOT NULL,
	[ModifiedDate] DATETIME2(7) NOT NULL
	);
GO

-- Add a clustered index on the AccountNTID column
CREATE CLUSTERED INDEX ix_AccountNT_AccountNTID ON [History].[AccountNT] (AccountNTID);
GO

Thirdly, we create an UPDATE and DELETE trigger to capture any changes to the table.

-- Update trigger
CREATE TRIGGER [TR_AccountNT_Update] ON [dbo].[AccountNT]
AFTER UPDATE, DELETE
AS
INSERT INTO [History].[AccountNT] (
	[AccountNTID],
	[AccountNumber],
	[AccountName],
	[Address],
	[Telephone],
	[CreateDate],
	[ModifiedDate]
	)
SELECT [AccountNTID],
	[AccountNumber],
	[AccountName],
	[Address],
	[Telephone],
	[CreateDate],
	[ModifiedDate]
FROM DELETED;
GO

Now, when we update a row in the [dbo].[AccountNT] table, the trigger will fire and insert a row into the history table using the DELETED.* information.

UPDATE [dbo].[AccountNT]
SET [AccountName] = N'Lorraine McFly',
	[ModifiedDate] = SYSUTCDATETIME()
WHERE [AccountNTID] = 1;
GO

covers6

The IAM and PFS operations are polluting the log, so let’s do a second update.

UPDATE [dbo].[AccountNT]
SET [AccountName] = N'Lorraine Baines McFly',
[ModifiedDate] = SYSUTCDATETIME()
WHERE [AccountNTID] = 1;
GO

covers7(Click to enlarge)

Look at the log records. They are identical to before. In fact, because a trigger is called in the same implicit transaction as the UPDATE statement, we didn’t even need to wrap the INSERT statement in a transaction.

What have we learned?

Temporal tables are doing exactly what an update or delete trigger (or stored procedure) would do, based on what we saw in the log records.

If you presently track data modifications using triggers and stored procedures, upgrading to SQL Server 2016 will save you a huge amount of time in creating and maintaining audit code.

Next time we will look at how temporal tables can be modified, once data has been written to them. Stay tuned.

An Introduction to Temporal Tables in SQL Server 2016 using a DeLorean

In 1983, Cyndi Lauper recorded the Billboard chart-topping single, Time After Time, which went on to win the hearts of many lovers in the three decades since then.

In 1985, Marty McFly used a DeLorean, invented by scientist Doc Emmett Brown, to travel back to 1955, where he met and interacted with his parents, Lorraine Baines and George McFly, before Lorraine and George fell in love.

In SQL Server 2016, we have a new feature, which I believe Microsoft has been working on for some time, called Temporal Tables.

I assure you that these are all related. In the words of Cyndi herself:

If you fall, I will catch you, I will be waiting
Time after time

Temporal tables allow us to retrieve the state of a table, at a specific point in time, using a method called effective dating. Not only useful for auditing and forensics, temporal tables can help if data is accidentally deleted, or perform trend analysis in a simpler way.

The next few posts will cover the broad strokes of this feature, explain how it works, and when to use it.

How Does It Work?

When we say that temporal tables are effective-dated, we need to jump into our DeLorean, and travel back to 1955 to look over the shoulder of Lorraine’s bank manager. When she opened her account in 1952, the [AccountName] column for her bank account would have said “Lorraine Baines”.

(I’m not getting into data models and a separate column for [FirstName] and [Surname] here — that’s a T-SQL Tuesday topic.)

Let’s assume this is the Accounts table:

-- IF EXISTS is a new SQL Server 2016 feature!
DROP TABLE IF EXISTS [dbo].[Account];
GO

CREATE TABLE [dbo].[Account] (
    [AccountID] INT IDENTITY(1, 1) NOT NULL,
    [AccountNumber] BIGINT NOT NULL,
    [AccountName] NVARCHAR(255) NOT NULL,
    [Address] NVARCHAR(1000) NOT NULL,
    [Telephone] NVARCHAR(20) NOT NULL,
    [CreateDate] DATETIME2(7) NOT NULL,
    [ModifiedDate] DATETIME2(7) NOT NULL,
    CONSTRAINT [PK_Account] PRIMARY KEY CLUSTERED ([AccountID] ASC)
    );
GO

-- Default constraints for the Create and Modified Dates
ALTER TABLE [dbo].[Account] ADD CONSTRAINT [DF_Account_CreateDate]
DEFAULT(SYSUTCDATETIME()) FOR [CreateDate];
GO

ALTER TABLE [dbo].[Account] ADD CONSTRAINT [DF_Account_ModifiedDate]
DEFAULT(SYSUTCDATETIME()) FOR [ModifiedDate];
GO

This is how it looks in my SQL Server Management Studio, as we’d expect:

account_pre_versioning

Now we insert our row:

INSERT INTO [dbo].[Account] (
    [AccountNumber],
    [AccountName],
    [Address],
    [Telephone]
)
VALUES (
    5551112233,
    N'Lorraine Baines',
    N'1727 Bushnell Avenue, Hill Valley, CA, 90101',
    N'310-555-1212'
);
GO

Later, when she got married to George, she took her husband’s last name and became “Lorraine McFly”. The database at the bank would have been updated with her new information, and that would be that. Whenever querying the accounts table, her bank manager would see her new name (or address, or telephone number).

UPDATE [dbo].[Account]
SET [AccountName] = N'Lorraine McFly',
    [ModifiedDate] = SYSUTCDATETIME()
WHERE [AccountID] = 1;
GO

What if we made a mistake, and wanted to see what her old name was? What if we wanted to track down when her name changed, or what her old address was, or see how many people changed their addresses over a five year period?

I have designed a few auditing and logging systems in my career, and the general consensus is to record how the data used to look, and store it somewhere. This might be done with a series of stored procedures or triggers, on each table, using DELETED.* data.

That in turn might be stored in an archive table or database, a read-only filegroup, or an XML table somewhere, that we would have to query separately to the primary table, perhaps doing a complex JOIN to get current data as well.

Being a custom solution, it would add maintenance overhead, contain bugs, and have to be wrapped in transactions and / or slow TRY ... CATCH blocks, to be on the safe side. We might have an update and delete trigger for each table, which means three times the pain when doing any Data Definition Language (DDL) operations.

Now let us imagine that the bank used temporal tables. All data modification (DML) changes would be applied in the same manner. However, aside from marking the table as a temporal table, with the concomitant columns, there is no need for a custom solution. No need for triggers or stored procedures to track changes. No need to maintain an archive database. No need to query archived data restored from tape or microfiche.

In fact, it is as simple as using additional parameters on a SELECT statement: FOR SYSTEM_TIME AS OF, and FROM ... TO (for example). If we give that parameter a date and time, we can see how the data looked on Lorraine’s account, at an exact point in time.

How Do I Make A Temporal Table?

We can create a new table as a temporal table, or we can convert an existing table (with a few limitations).

Temporal tables require a clustered index. That said, I think every table in your database should have a clustered index anyway.

SQL Server 2016 Books Online shows us the dry syntax for creating new, or modifying an existing table, to make use of this effective dating, but let’s walk through it together here as well.

We need two new columns in our table. These should be DATETIME2(7), which adds an additional 16 bytes to our row length.

Keep this in mind for tables with a lot of churn, because we will generate a lot of data that has to be written to the history table, and 16 bytes adds up for millions or billions of changes.

Nevertheless, if you already implement an archival system, and keep track of when rows change (using a CreateDate and ModifiedDate column for instance), you’re already halfway there.

While we can choose the names of our effective dated columns, the documentation uses SysStartTime and SysEndTime, which I will also use in this post.

The SysStartTime will record when the row was created or modified (the effective date of the change). This is an important distinction from, say, a column that records just the CreateDate of a row. If we need to know the CreateDate of a row, we may be more comfortable leaving in our CreateDate column and adding the two new columns to that.

Of course, if we have a ModifiedDate-type column, that falls away entirely under this design. Win!

Once we’ve created our start and end time columns, we need to tell SQL Server to convert the table to a temporal table.

This is done in two stages. Firstly, we have to tell SQL Server that the two new columns will be the start and end of a temporal period.

-- Adds two new PERIOD columns and sets the temporal rules
ALTER TABLE [dbo].[Account] ADD PERIOD
FOR SYSTEM_TIME([SysStartTime], [SysEndTime]),
    [SysStartTime] DATETIME2(7) GENERATED ALWAYS AS ROW START
        HIDDEN NOT NULL
        CONSTRAINT [DF_Account_SysStartTime]
        DEFAULT SYSUTCDATETIME(),
    [SysEndTime] DATETIME2(7) GENERATED ALWAYS AS ROW END
        HIDDEN NOT NULL
        CONSTRAINT [DF_Account_SysEndTime]
        DEFAULT CONVERT(DATETIME2, '9999-12-31T23:59:59.9999999');
GO

Notice that SysEndTime is set to the largest possible value allowed for a DATETIME2 column. This is how SQL Server knows (and we know) that the row is current when viewing the data.

(The HIDDEN property is optional and will hide these columns from a standard SELECT statement for backward compatibility with our application and queries. You cannot apply the HIDDEN property to an existing column.)

Secondly, we must enable SYSTEM_VERSIONING, and define a history table to store the historic values. While we can create a history table ourselves (and if we have storage considerations, this might be a good idea so that the data is stored in a different file group), we only need to define a table name. If it doesn’t exist, SQL Server will create it for us. If it does exist, and has the same structure (column names can be different), it will use that table.

-- Make sure the History schema exists
ALTER TABLE [dbo].[Account]
SET (
    SYSTEM_VERSIONING = ON (
        HISTORY_TABLE = [History].[Account],
        DATA_CONSISTENCY_CHECK = ON
    )
);
GO

As Books Online states, it is “highly recommended to set SYSTEM_VERSIONING with DATA_CONSISTENCY_CHECK = ON to enforce temporal consistency checks on existing data.”

As soon as we run the second ALTER TABLE command, and the history table is created or checked, we will now have a temporal table. It’s a metadata operation.

This is how it looks in my SQL Server Management Studio. Notice that the History table was created automatically, based on the structure of the primary table:

account_post_versioning

Now all changes are recorded by the database, in a history table, which is directly linked to the primary table, without needing any triggers or stored procedures.

It is not possible to externally modify the history table while it is linked to the primary table. Any change to the primary table is recorded in the history table by recording what the row looked like before the change.

And querying is a breeze. The main benefit for my use is to see what data looked like at a specific point in time, but we can also see what the table did between a start and end time, how the rows changed, and so on. There are performance considerations for each.

Let’s change Lorraine back to Baines and see how it looks:

UPDATE [dbo].[Account]
SET [AccountName] = N'Lorraine Baines',
    [ModifiedDate] = SYSUTCDATETIME()
WHERE [AccountID] = 1;
GO

-- SysStartTime and SysEndTime explicitly named, or they will
-- not show up in the results, as they are hidden columns
SELECT [AccountID],
    [AccountName],
    [AccountNumber],
    [Address],
    [Telephone],
    [CreateDate],
    [ModifiedDate],
    [SysStartTime],
    [SysEndTime]
FROM [dbo].[Account] FOR SYSTEM_TIME AS OF '2015-11-08T21:45:00';
GO

SELECT [AccountID],
    [AccountName],
    [AccountNumber],
    [Address],
    [Telephone],
    [CreateDate],
    [ModifiedDate],
    [SysStartTime],
    [SysEndTime]
FROM [dbo].[Account] FOR SYSTEM_TIME BETWEEN '2015-11-01' AND '20151231';
GO

Here are the two result sets. You can click on the image to view it full size:

account_post_select

The first result shows the data before the UPDATE ran. Notice how SysEndTime is the same value as the primary row’s SysStartTime and identical to the ModifiedDate value. This is why I stated previously that the ModifiedDate column is now redundant. We can convert this to a persisted computed column, based on SysStartTime.

In my next post, I will cover some of the things happening under the covers to make this magic work.