Maintaining LastModified Without Triggers
For a long time, we have been using defaults for columns to reflect "created," but we've had to resort to triggers to handle updates to reflect "last modified." Wouldn't it be nice if SQL Server had something like MySQL's
CREATE TABLE `posts` ( PostID INTEGER, LastModified TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP )
Instead, we have to code and maintain after (or instead of) update triggers, and deal with multiple patterns that can either cause bugs after initial testing (like when someone assumes the trigger will only ever handle a single row) or force a logic change (like when someone uses
@@ROWCOUNT but then starts using
MERGE, against all good reason).
Since SQL Server 2016, we've had the ability to use Temporal Tables to keep historical versions of rows. Well, you can use some Temporal Table functionality without actually turning the feature on, as I show in this quick example:
CREATE TABLE dbo.Posts ( PostID int NOT NULL PRIMARY KEY, Title nvarchar(255) NOT NULL, Created datetime2 NOT NULL DEFAULT sysutcdatetime(), LastModified datetime2 GENERATED ALWAYS AS ROW START NOT NULL, Garbawgy datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL, PERIOD FOR SYSTEM_TIME (LastModified, Garbawgy) ); GO INSERT dbo.Posts(PostID) VALUES(1),(2); WAITFOR DELAY '00:00:05'; UPDATE dbo.Posts SET Title += N'a' WHERE PostID = 2; SELECT * FROM dbo.Posts;
PostID Title Created LastModified ------ ---------- --------------------------- --------------------------- 1 Post 1 2021-04-01 16:47:57.5717665 2021-04-01 16:47:57.5707663 2 Post 2a 2021-04-01 16:47:57.5717665 2021-04-01 16:48:02.5733807
Garbawgy doesn't show up unless you explicitly ask for it.
There are some limitations that you need to be aware of:
SYSTEM_TIMEis always in UTC (which I prefer anyway, and which is why I also use that for
- The time recorded is the start of the transaction, so sometimes the time won't accurately reflect the last change.
- The extra hidden column is not free.
- You can't update either column manually (still waiting for
- You can't just drop the
Garbawgycolumn without making other changes first.
If you got this far without laughing…
This is totally a prank on Michael J. Swart, who was embarrassed about posting on a topic that several other people had already written about:
- Erik Darling : Tracking Row Changes With Temporal Columns
- Daniel Hutmacher : How to add “created” and “updated” timestamps without triggers
- Martin Smith : Need a datetime column that automatically updates …
I don't think he should be embarrassed, in fact I think this is great: because everyone describes the scenario in a slightly different way, and that way could be the thing that makes the lightbulb click for a reader.
And I am far from innocent of things like this… I sometimes blog about things I've already blogged about. I'm also often surprised when I search for some topic and I land on a blog post I completely forgot I ever wrote.
Anyway, happy belated and back-dated April Fools'!