How many times over your career have you had to create some form of audit trail functionality within a system or project you’ve worked on? The answer for me is often. Depending on your preferences, and probably experience, there’s several ways of doing so.
There’s a few obvious points you could implement auditing, each of which have good points and bad points:
You could log from your User Interface tier, although that could prove problematic when you consider many system operations are initiated elsewhere. Your services layer or middle tier could be responsible for logging, if every operation were routed through that layer. There are many options.
In the past, one example of system auditing I’ve been involved with, worked at the Relational Database Tier on SQL Server. Now before people recoil in horror, I will mention that it utilized Triggers. I know that the vast majority of people live by the “oft repeated wisdom” that Triggers are evil things that should be avoided at all costs! And yes, in many instances, poorly written triggers can be a cause of much pain and aggravation.
However, with suitable skill, triggers can be written efficiently and safely. And in this way, we can use triggers to implement an appropriate database level auditing system. With suitable care, we can also write scripts to generate the required triggers whenever table schema changes occur. BUT, I’m not writing to suggest using triggers for auditing, and nowadays there are some not insignificant downsides to triggers. They are now considered an “old-fashioned” feature, and are incompatible with some of the newer SQL Server features, such as Memory Optimized Tables. So if we continue to use them, we limit ourselves from exploiting some of these newer features.
That is why I was very interested by the introduction in SQL Server 2016 of a new feature called System Versioned Temporal Tables or simply Temporal Tables. This feature provides a way for transparently keeping a history of the changes to one or more tables in a SQL Server database. And interestingly given the way Temporal Tables are structured, you can reconstruct the state of the data in these tables at any point in time. And in my experience, I’ve found it’s a feature that very few people seem to know much about. Given how much value it can deliver, I think more people should know about it!
The key word in the previous paragraph is transparently. Any data change operations that are performed against the data in the Temporal Table occur just as they normally would – any Insert, Update or Delete statements are executed by whatever client application as per normal. SQL Server takes care of the changes to the Primary Table – what is now called the Temporal Table and the older versions of rows modified are kept in the corresponding History Table so that the history of any changes – essentially an audit history of any changes – is maintained on your behalf. The important thing to note here is that there is no change whatsoever to the way any client application “interacts” with the database. There should be no change required to any well-behaved client application at all.
So how do we start to use Temporal Tables I hear you ask? Well, there are some additional columns required on the primary or Temporal table, and a secondary History table is required, as you would imagine, but after that, SQL Server takes over. Let’s examine how we would create a Temporal Table from scratch. The following statements create a relatively simple reference type table, intended to store details of countries. I’ll use this table in some examples below.
CREATE TABLE [dbo].[Country]( [CountryId] [int] IDENTITY(1,1) NOT NULL, [CountryCode] [char](3) NOT NULL, [CountryCode2] [char](2) NOT NULL, [CountryNameOfficial] [nvarchar](100) NOT NULL, [CountryName] [nvarchar](25) NULL, [InternetTLD] [char](3) NULL, [CreatedAt] [datetime2](7) NOT NULL, [ModifiedAt] [datetime2](7) GENERATED ALWAYS AS ROW START NOT NULL, [ValidTo] [datetime2](7) GENERATED ALWAYS AS ROW END NOT NULL, PRIMARY KEY CLUSTERED ( [CountryId] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], PERIOD FOR SYSTEM_TIME ([ModifiedAt], [ValidTo]) ) ON [PRIMARY] WITH ( SYSTEM_VERSIONING = ON ( HISTORY_TABLE = [dbo].[CountryHistory] ) ) GO ALTER TABLE [dbo].[Country] ADD DEFAULT (getutcdate()) FOR [CreatedAt] GO
There are a number of points to note about the statement.
- Temporal Tables must always have a period of validity associated with each row. This period of validity is maintained by SQL Server as you create and modify rows in the table. The PERIOD FOR SYSTEM_TIME ([ModifiedAt], [ValidTo]) clause defines which columns represent the validity period for our Temporal Table.
- What I have found is that as I often have columns called [CreatedAt] and [ModifiedAt] on tables, so I can simply re-purpose the [ModifiedAt] column to be start of that period of validity.
- Note that for the currently valid row – the row that remains in the Temporal Table, the Primary Table, the end of the period of validity will be the maximum value that a datetime2 datatype can contain – which is 23:59:59 31/12/9999 – essentially infinitely into the future. So that is meant to imply that the current row is valid from the time it was last modified infinitely into the future.
- However, when the current row does get changed, the following is performed transparently by SQL Server:
- A version of row prior to any changes gets transcribed into the History Table. Remember, the History Table’s structure is absolutely identical to the Temporal or Primary Table’s structure.
- The transcribed copy has its Valid To column set to the date and time the modification was made to that original row.
- The row that was changed in the Temporal Table, gets its ModifiedAt date set to the date and time of modification.
- In the case of a SQL Server Delete statement, the row is removed from the Temporal Table, and the version of the row prior to deletion remains in the History table with the last ValidTo value representing the date and time of deletion.
- When creating a Temporal Table it is not mandatory to name the History Table, as SQL Server will provide a default name, however I would strongly recommend you do, as the default names that SQL Server will create for you are not particularly useful, being generated only to guarantee uniqueness.
- One of the most important features of System Versioned Temporal Tables is the way SQL Server manages Schema changes and the maintenance of the tables and their changes.
- It will always ensure the schema of the Temporal Table and the History Table remain synchronized.
- It will keep datatypes of columns synchronized, and will allow widening of datatypes that ensures no data will be lost in either the Temporal Table or the History Table
- However SQL Server will not allow any narrowing datatype changes the could result in data loss in either the Temporal Table or the History Table. This includes both changes such as float => int, or nvarchar => char, or char(100) => char(50) where data exists that exceeds 50 characters in length.
- It won’t allow you to drop columns from the Temporal or Primary Table that have historical data in them.
- You won’t be able to simply drop Temporal Tables, without taking specific action to consciously disable system versioning on them. This clearly avoids any inadvertent loss of critical System Versioned Temporal Tables.
How do System Versioned Temporal Tables appear within Enterprise Manager? The Temporal Table I created above, is called Country and its related History Table is called CountryHistory. I’ve seeded it with some data including Country Name, Country Codes and the like.
As noted in the screen shot, the CountryHistory History Table appears as a “child” to the Country Temporal Table. You will never have to do anything to maintain or manage the History Table. Once you’ve defined your Temporal Table, SQL Server manages all aspects of the creation, modification and any other manipulation of the History Table. All you should ever do with the History Table, is query it. Now, as I’ve just added all of the rows above to the Temporal or Primary Table, there are no rows currently present in the History table. All of the rows only exist in the Temporal Table, as they are the only “versions” of the rows – the “first versions” of these rows.
But if we perform an update to a row in the Temporal Table, we will see how SQL Server manages these two tables. Let’s update the Internet Top Level Domain for Australia – that’s what the TLD column contains. Currently, this column contains a lower-case value, as shown by the following statement:
Now if we update the value to be upper case and verify that this has been done by reviewing the data in the row, you’ll note that the only thing that has changed, other than the InternetTLD column, is the ModifiedAt column.
Remember that this change to the ModifiedAt column has occurred not because of some default or trigger, but as a result of the definition of the table as a Temporal Table, and that the ModifiedAt column is the Period Start Column of our Validity Period range.
To view the entire history of changes for a row, you can simply combine the contents of the Temporal Table with the History Table, remembering that they are structured identically. So take our particular rather contrived example above. If we wanted to view all changes to our row, we can simply union the two tables together.
In conclusion, I’ll leave you with some final thoughts and comments:
- Transparent Auditing: System Versioned Temporal Tables can provide a relatively transparent form of auditing for SQL Server based systems and applications, which can be invisible to that system or application above. The tables updated behave no differently.
- Entity Framework Support for Temporal Tables: There are a variety of issues relating to using Temporal Tables with EF / EFCore:
- 3rd Party Libraries are required to get EF / EFCore to support the syntax extensions for creating / modifying System Versioned Temporal Tables. For those uncomfortable using 3rd Party libraries until libraries such as EF and EFCore have 1st Party support, this might be a deal breaker.
- EF / EFCore don’t provide intrinsic support for querying the combination of the Temporal Table with its History Table.
- I DO NOT mean that Entity Framework can’t update Temporal Tables, of course it can, because they don’t behave any differently to any other tables.
- Performance Impacts: I don’t think there would be any substantial performance impacts as the transcription of rows into the History Table, with no indexing would be very quick operation. However the very quick nature of that operation is a pointer to how problematic it could be querying that History Table as the volume of data in it grew significantly. Some recommendations are that this table is only an intermediate repository for audit data, and that change data is “moved” to a separate repository more tuned for querying.
Having recently implemented System Versioned Temporal Tables to provide what is essentially “out of the box” auditing for an application, I’d highly recommend you take a look at this feature of recent SQL Server editions. It may not suit all situations, but I think it’s a very valuable tool to have in the toolbox.
For a good discussion, take a look at the following Microsoft docs on System Versioned Temporal Tables.
TA's Ramblings 