Thursday, March 8, 2012
@@Trancount in DML statement
I was debuging something related to transactions
and saw something strange from first sight.
You know that @@TRANCOUNT returns
the number of BEGIN TRANSACTION
statements that have occurred on the current
Well, check this situation:
CREATE TABLE dbo.TempTbl (Info VARCHAR(10))
INSERT INTO dbo.TempTbl(Info)
So why the value of the @@TRANCOUNT is 2?
I did not use BEGIN TRANSACTION.
I undestand that INSERT is implicit transaction and
therefore the @@TRANCOUNT can be 1, by why it equals to 2?
The reason is that the @@TRANCOUNT counts the user transaction
as well as the inner transaction.So in DML statement it will be always at least 2.
Here is more, when using a variable table the @@TRANCOUNT is 0:
This is because local variables(and tables) are not effected
For more info: