sys.dm_tran_current_transaction. How unique is transaction_id

Question

I am capturing the transaction_id from sys.dm_tran_current_transaction in my audit triggers. What I would like to know is the uniqueness of transaction_id. The documentation only says "Transaction ID of the current snapshot".

I can only assume that this "ID" will be reused at a later time, but i would appreciate some confirmation on this.

Answer 1

Reference details can be checked at this link

Answer 2

@abcdefghi's answer is good, but just to add to it;

• Transaction_id is the same within a transaction,
• outside an explicit transaction, transaction_id updates on each batch,
• transaction_id resets when the server resets, so you will encounter the same transaction_id over time.

What this leads to is that you can't use transaction_id in audit tables, because of this scenario;

audit record inserted with transaction_id = 42 sql server reset ... 41 new batches executed ... audit record inserted with transaction_id = 42

Also, you can't use transaction_id like a timestamp (higher values do not imply later changes_ and you can't say that the same ID implies the same transaction.