You have a table that you want to add “created” and “updated” timestamp columns to, but you can’t update the application code to update those columns. In the bad old times, you had to write a trigger to do the hard work for you. Triggers introduce additional complexity and potentially even a performance impact.
So here’s a nicer way to do it, trigger-free.
How we used to do it
Suppose we have this table, to which we’ve added two columns, Created and Updated.
CREATE TABLE dbo.SomeTable (
a int NOT NULL,
b int NOT NULL,
c varchar(100) NOT NULL,
d date NOT NULL,
Created datetime2(7) DEFAULT (SYSUTCDATETIME()) NOT NULL,
Updated datetime2(7) DEFAULT (SYSUTCDATETIME()) NOT NULL,
CONSTRAINT PK_dbo_SomeTable PRIMARY KEY CLUSTERED (a, b)
);
We’ve put a DEFAULT constraint on both columns, so when we’re inserting new rows, those two columns will by default be prepopulated with the current date and time. For updates, however, you’ll need to add a trigger.
CREATE OR ALTER TRIGGER dbo.SomeTable_Update
ON dbo.SomeTable FOR UPDATE
AS
SET NOCOUNT ON;
UPDATE st
SET st.Updated=SYSUTCDATETIME()
FROM dbo.SomeTable AS st
INNER JOIN inserted AS i ON st.a=i.a AND st.b=i.b;
I went with an “after” trigger, even though it adds an extra operation. If performance matters, you may want to build an “instead of” trigger, but just know that it adds a lot of complexity when it comes to handling things like updates to the primary key columns. This could actually result in an even greater performance impact than the original “after” triggers.
Non-versioned temporal tables
A great solution is to use a temporal table.
CREATE TABLE dbo.SomeTable (
a int NOT NULL,
b int NOT NULL,
c varchar(100) NOT NULL,
d date NOT NULL,
Created datetime2(7) DEFAULT (SYSUTCDATETIME()) NOT NULL,
Updated datetime2(7) GENERATED ALWAYS AS ROW START NOT NULL,
ValidUntil datetime2(7) GENERATED ALWAYS AS ROW END NOT NULL,
CONSTRAINT PK_dbo_SomeTable PRIMARY KEY CLUSTERED (a, b),
PERIOD FOR SYSTEM_TIME (Updated, ValidUntil)
);
“Hold you horses”, I hear you say, “I don’t need to version the data, I just want the timestamps.”
Temporal tables are not system versioned until you actually add a history table. As long as we don’t add the WITH (SYSTEM_VERSIONING=ON) clause, we just get to use the GENERATED ALWAYS columns to keep track of the update timestamp.
Quirks and features of temporal tables
UTC time, not local
For temporal tables, the row start and row end columns must be defined as datetime2 (though you can set any precision you like). However, the generated values are in UTC time, not in the server’s local time zone.
This obviously makes a lot of sense when you use history tables, since you don’t want them to break when your server goes from daylight savings time back to normal time. For our purposes, it’s something to keep in mind if you’re used to working with local time.
Transaction time, not change time
This is a weird one if you haven’t seen it before. The timestamp you see in the “Updated” column will be the start of your transaction. This is true for both implicit and explicit transactions – especially if the INSERT or UPDATE is a long-running statement, the “Updated” column will reflect the start time of that transaction or long-running statement, not the time the statement ran or completed.
Here’s an illustration:
SET NOCOUNT ON;
TRUNCATE TABLE dbo.SomeTable;
INSERT INTO dbo.SomeTable (a, b, c, d)
VALUES (1, 2, '3', {d '2021-08-07'});
BEGIN TRANSACTION;
PRINT 'The time is '+CONVERT(varchar(100), SYSUTCDATETIME(), 121)+' at BEGIN TRANSACTION.';
WAITFOR DELAY '00:00:10';
PRINT 'The time is '+CONVERT(varchar(100), SYSUTCDATETIME(), 121)+' at UPDATE.';
UPDATE dbo.SomeTable
SET b=3, c='4'
WHERE a=1 AND b=2;
WAITFOR DELAY '00:00:10';
PRINT 'The time is '+CONVERT(varchar(100), SYSUTCDATETIME(), 121)+' at COMMIT TRANSACTION.';
COMMIT TRANSACTION;
DECLARE @ts datetime2(7)=(SELECT TOP (1) Updated
FROM dbo.SomeTable
WHERE a=1 AND b=2);
PRINT 'The update timestamp is '+CONVERT(varchar(100), @ts, 121)+'.';
The time is 2021-08-07 11:47:39.2498421 at BEGIN TRANSACTION.
The time is 2021-08-07 11:47:49.2525526 at UPDATE.
The time is 2021-08-07 11:47:59.2928030 at COMMIT TRANSACTION.
The update timestamp is 2021-08-07 11:47:39.2498421.
Keep this in mind, as you may encounter rows where the “created” timestamp (which is a regular column default) is slightly later than the “updated”, because of this phenomena.
Start and end columns are not updatable
This may be a problem or a feature depending on your use case. SQL Server does not allow you to update these special columns because they’re used for system versioning (which we don’t technically use here, but still).
Hiding left-over columns
When you create a temporal table, you need to specify both a “row start” and “row end” column, and those columns will live in both the base table and the history table (if any). For our purpose, the “row end” column in the base table will by definition always contain the timestamp 9999-12-31 23:59:59.9999999, so we don’t really need or want the column hanging around.
The bad news is that we can’t drop the column. The good news is we can hide it.
ALTER TABLE dbo.SomeTable ALTER COLUMN ValidUntil ADD HIDDEN;
This hides the column when you perform a SELECT *. It doesn’t delete the column, and you can still query the column if you explicitly specify it.
If you want to hide the column in the CREATE TABLE statement, add “HIDDEN” after right after “ROW END”. You can hide the “row start” column as well if you want to. Only GENERATED ALWAYS columns can be hidden at the time of writing this (2021).
CREATE TABLE dbo.SomeTable (
a int NOT NULL,
b int NOT NULL,
c varchar(100) NOT NULL,
d date NOT NULL,
Created datetime2(7) DEFAULT (SYSUTCDATETIME()) NOT NULL,
Updated datetime2(7) GENERATED ALWAYS AS ROW START NOT NULL,
ValidUntil datetime2(7) GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
CONSTRAINT PK_dbo_SomeTable PRIMARY KEY CLUSTERED (a, b),
PERIOD FOR SYSTEM_TIME (Updated, ValidUntil)
);
Hiding version columns is a great way to ensure compatibility if your legacy application uses things like SELECT *, or if it does INSERTs without explicitly naming the columns.
Word of warning: scripting
Temporal tables have been around since SQL Server 2016, but I’ve found that not all scripting solutions are up-to-date with all of the minute details. For me, the PowerShell SqlServer scripting module omitted the “HIDDEN” keyword.
Nice post.
I also posted about this possibility a while back and found some people are nervous about using just the “PERIOD FOR SYSTEM TIME” feature without the temporal table feature.
(as in the comments here
https://stackoverflow.com/questions/21493178/need-a-datetime-column-in-sql-server-that-automatically-updates-when-the-record/47487144#47487144 )
Thanks! I guess if the feature has been around for five years and three major versions, it’s not likely that they’ll yank it any time soon. 🙂
Really, really nice trick. Thanks for posting, Daniel! Well done!
The problem for me is that is only solves half the problem even if I’m using a history table. The problem is, how to auto-magically update the proverbial “Last_Modified_By” column without the use of a trigger. And, yeah..l. I’m really ticked at MS for not including something so obvious in system versioned tables.
Haha, yeah, been there. The date is a good start just to see when (what batch/date) something changed. Not great for auditing changes, since the row could change multiple times..
Oooh interesting trick, thanks! Is there a performance overhead for maintaining these columns? I’d expect it to be better than triggers, but with Microsoft you never know 🙂
I haven’t benchmarked it, but the overhead seems to be negligible compared to the actual writes.
Okay, I ran some basic tests:
table with nothing, update 100k rows: 150-200ms cpu, 987 reads, 981 writes
table with updatedate trigger, update 100k rows: 650-700ms cpu, ~500k reads, ~9k writes
table with non-versioned temporal table columns, update 100k rows: 150-200ms cpu, 1093 reads, 1087 writes
So it seems like the temporal columns added ~10% IO overhead in my tests, but that’s of course nowhere near the overhead of the trigger. For single-row updates this is all much less important, unless it’s a system with a very write-intensive workload. Interesting!
@Alex Friedman wrote “but that’s of course nowhere near the overhead of the trigger”.
Any chance of you sharing what the trigger code was?
Hi Jeff, yeah it was the same as in this article (just slightly different columns in the demo table I had lying around, but still joining by the clustered PK etc.).
Regarding performance the maintenance of the “PERIOD FOR SYSTEM_TIME” columns just gets integrated into the execution plan along with the INSERT/UPDATE so it adds no more overhead than updating these columns manually in the corresponding INSERT/UPDATE would.
The trigger of course means that each row is written twice – once for the original DML operation and again for the update of the time stamps.
Thanks, Alex. Man, I’m really surprised your findings of 4 times more CPU, 500 times more reads, and 9 times the writes. I knew it was going to be more than just a normal insert but holy-moly! Looks like I have a bit of extra testing to do because THAT finding is a killer argument against any nay-sayers I might run into in the future.
Thanks for testing and reporting your findings.
Awesome, I’ll be happy to see your findings too! I was pretty surprised myself. I haven’t tested it for single row updates though, I expect the effect to be much lighter there. I’ll try to find some time to run that too.
BTW, you probably are already aware of this, but to measure writes properly in the trace/XE, run checkpoint first 🙂
Oh yeah… well aware of that nuance in testing. I really got burned by it in my early days.
Did some more testing on single row updates:
Plain table: 0 cpu, 3 reads, 1 write
Table with trigger: 0 cpu, 10 reads, 1 write (sometimes 15 reads and 10 writes, not sure why)
Table with non-versioned temporal table columns: 0 cpu, 3 reads, 1 write
Very nice how the temporal columns had absolutely no effect on the single-row update’s performance. The trigger was lighter compared to 100k rows, as expected, but still considerably more resource-intensive than the alternative.
Oh, and a note for any readers who might want to use the updated column for reading a table incrementally — this might be obvious, but because the temporal columns store the transaction start time as Daniel wrote, you can’t rely on them for an incremental process. You’ll read the max updated datetime, and then next time you want to continue with higher values, but then you miss data that was changed in a transaction that started earlier, but finished after that time, as it will have a lower updated datetime.