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
Last activity: Nov 30, 2024, 10:20 PM