Sample Header Ad - 728x90

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

0 votes
3 answers
191 views
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.
Asked by Maury Markowitz (230 rep)
Nov 29, 2024, 05:57 PM
Last activity: Nov 30, 2024, 10:20 PM