The CURRENT_TRANSACTION_ID function, as its name suggests, returns the transaction ID of the current transaction. The scope of the transaction is the current session. It has the same value as the transaction_id column in the Dynamic Management Views (DMV) sys.dm_tran_current_transaction. The function has no input arguments and the returned value is of type bigint.
Multiple calls of this function will result in different transaction numbers, since every single call is interpreted as an implicit transaction:
SELECT CURRENT_TRANSACTION_ID(); SELECT CURRENT_TRANSACTION_ID(); BEGIN TRAN SELECT CURRENT_TRANSACTION_ID(); SELECT CURRENT_TRANSACTION_ID(); COMMIT
The result on my machine is as follows (you will definitely get different numbers, but with the same pattern):
------------- 921170382 921170383 921170384 921170384
There is also the SESSION_ID function, which returns the current session ID, but it works only in Azure SQL Data Warehouse and in Parallel Data Warehouse. When you call it in an on-premises instance of SQL Server 2017, instead of the current session ID, you will see the following error message:
Msg 195, Level 15, State 10, Line 1 'SESSION_ID' is not a recognized built-in function name.
You can use the CURRENT_TRANSACTION_ID function to check your transaction in the active transactions as follows:
SELECT * FROM sys.dm_tran_active_transactions WHERE transaction_id = CURRENT_TRANSACTION_ID();