» » @@Trancount in DML statement

@@Trancount in DML statement

Edit

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
connection, right?

Well, check this situation:


CREATE TABLE dbo.TempTbl (Info VARCHAR(10))
GO

INSERT INTO dbo.TempTbl(Info)
SELECT @@TRANCOUNT
GO

SELECT * FROM dbo.TempTbl
 
Here is the resut set:

 
 
 
 
 
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
by transaction(s).


For more info:

http://blogs.msdn.com/b/jenss/archive/2010/07/10/usage-of-trancount-in-dml-statements.aspx