I like rowversion columns, I like that they’re system-maintained and they provide a unique deterministic way to compare or order changes in a database. But they’re not timestamps (despite the alias).
I also like datetime2 columns which are called LastModifiedDate. They can indicate the date and time that a row was last modified. But I have to take care of maintaining the column myself. I either have to remember to update that column on every update or I have to use something like a trigger with the associated overhead.
But maybe there’s another option.
GENERATED ALWAYS AS ROW START
What if I use the columns that were meant to be used for temporal tables, but leave SYSTEM_VERSIONING off?
CREATE TABLE dbo.Test ( Id INT IDENTITY NOT NULL, Value VARCHAR(100) NOT NULL, LastModifiedDate DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL, SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL, PERIOD FOR SYSTEM_TIME (LastModifiedDate, SysEndTime), CONSTRAINT PK_Test PRIMARY KEY CLUSTERED (Id) ) |
It’s system maintained, it’s an actual datetime and I don’t have to worry about the overhead of triggers.
But it’s not a perfect solution:
SysEndTime
is required but it’s an unused column here. It’s needed to define the period for SYSTEM_TIME and it’s always going to be DATETIME2’s maximum value. That’s why I made that column “hidden”. It’s got an overhead of 8 Bytes per row.- The value for LastModifiedDate will be the starting time of the transaction that last modified the row. That might lead to confusing behaviors illustrated by this example. Say that:
- Transaction A starts
- Transaction B starts
- Transaction B modifies a row
- Transaction A modifies the same row
After all that, the last modified date will indicate the time that transaction A starts. In fact if I try these shenanigans with system versioning turned on, I get this error message when transaction A tries to modify the same row:
- Msg 13535, Level 16, State 0, Line 16
Data modification failed on system-versioned table ‘MyDb.dbo.Test’ because transaction time was earlier than period start time for affected records.
Everything is tradeoffs. If you can live with the drawbacks of the system-generated last modified date column, then it might be an option worth considering.
Brilliant.
Comment by Bruce Scanlan — October 1, 2021 @ 4:27 pm
Worth mentioning that DATETIME2(3) which has millisecond granularity, only needs 7 bytes per column per row, as opposed to the 8 needed by DATETIME2(7).
Comment by Randolph West — December 13, 2021 @ 6:20 pm