Using CURRENT_TRANSACTION_ID

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 (DMVsys.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(); 
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.226.34.25