A good way to get "transaction time" or similar on SQL Server for change tracking

Question

I would like to add version information to our DB - all it will be used for is to check whether the user's version of a row has changed since they read it. I am thinking along the lines of adding a column called _VERSION to the two dozen tables in question. As part of a batch UPDATE statement, I'd have something to the effect of SET ..._VERSION=something_or_other. I am agnostic on what data this column holds.

One solution I've seen is to use a datetime column and then get the transaction start time from sys.dm_tran_active_transactions, selecting the row for CURRENT_TRANSACTION_ID(). For our needs, the 1/300th accuracy will be more than enough. I understand that using CURRENT_TIMESTAMP is not as useful in this situation because that will change as the batch proceeds.

If this is canonical I'll do that. It's easy to implement.

But is this the best way for really simple uses? Is there some other value in the sys.dm_tran... that provides the same outcome but may be easier to store and WHERE than a datetime?

NOTE: yes, I am aware of temporal tables and MS's change tracking and have been told not to use them.

Answer 1

check whether the user's version of a row has changed since they read it

You don't need any transaction time.

rowversion type is introduced for it

The rowversion data type is just an incrementing number and does not preserve a date or a time.

Answer 2

Another answer suggests adding a rowversion column to your tables. That can work just fine, but you may be unhappy if your tables are large and highly transactional, as there may be significant blocking adding those columns in. There aren't any great tricks or shortcuts for that either, that I've seen.

You may be better off adding a non-NULLable column with a default value to your tables—datetime2(0) would be fine—as that is a metadata-only change for most modern SQL Server versions (unlike the rowversion change) and use AFTER triggers to keep track of successfully completed UPDATE statements. You don't need them for INSERT or DELETE queries for obvious reasons:

• Any insertions will get the default SYSDATETIME() for when they're inserted
• Physical deletions (not soft deletes) will remove the row entirely

Makes things rather simple.

scripted

For the column:

ALTER TABLE
    dbo.Users
ADD
    LastModified datetime2(0) NOT NULL
    DEFAULT SYSDATETIME();

For the trigger:

CREATE OR ALTER TRIGGER
    dbo.User_Updates
ON dbo.Users
AFTER 
    UPDATE
AS
BEGIN 
    IF ROWCOUNT_BIG() = 0
    BEGIN
        RETURN;
        /*Bail early if no rows*/
    END;
/*
Set these in case they've been 
altered in an outer context
*/
SET NOCOUNT, XACT_ABORT ON;
SET ROWCOUNT 0;

IF UPDATE(LastModified)
BEGIN
    RETURN;
    /*
    Bail if someone modifies 
    the column manually
    */
END;

/*Update any qualifying rows*/
UPDATE
    u
SET
    u.LastModified = SYSDATETIME()
FROM dbo.Users AS u
WHERE EXISTS
(
    SELECT
        1/0
    FROM Inserted AS i
    WHERE u.Id = i.Id
);

END;

Testing, 1-2-3:

BEGIN TRANSACTION
    SELECT
        u.Id,
        u.Age,
        u.LastModified
    FROM dbo.Users AS u
    WHERE u.Id = 22656;
UPDATE
    u
SET
   u.Age = 138
FROM dbo.Users AS u
WHERE u.Id = 22656;

SELECT
    u.Id,
    u.Age,
    u.LastModified
FROM dbo.Users AS u
WHERE u.Id = 22656;

ROLLBACK TRANSACTION;

Results:

For the trigger, you could use either the Inserted or Deleted virtual table. It doesn't matter here, because they'll both contain the same basic set of rows.

It would matter if you wanted the before picture (Deleted table) or after picture (Inserted table) to use the results for auditing the actual changed values.

Answer 3

A brilliant answer on StackOverflow by @MartinSmith uses a CDC feature, but without actually applying CDC to the table.

CREATE TABLE dbo.YourTable
(  
    FooId INT PRIMARY KEY CLUSTERED,   
    FooName VARCHAR(50) NOT NULL,
    modstamp DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,   
    MaxDateTime2 DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,     
    PERIOD FOR SYSTEM_TIME (modstamp,MaxDateTime2)    
);

It has some limitations.

• The time stored will be updated by the system and always be UTC.
• There is a need to declare a second column (MaxDateTime2 above) that is completely superfluous for this use case. But it can be marked as hidden making it easier to ignore.

Note that there is no Temporal Tables history table here, it's just a pair of auto-update columns. So this shouldn't be an issue if you are concerned about Temporal Table performance.