Some time ago I got an interesting questions why the following applies:
--drop table a create table a(m varchar(max), i int) go declare @cnt int --Explicit transaction begin tran set @cnt=@@trancount insert into a select 'inside', @@trancount insert into a select 'inside cnt', @cnt commit tran set @cnt=@@trancount --Implicit transcation insert into a select 'outside', @@trancount insert into a select 'outside cnt', @cnt
select * from a
The output is:
inside cnt 1
outside cnt 0
So why is that? The reason is that the trancount counts the user transaction as well as the inner transaction. You can also see that if you take a look at the transaction log for that. I a DML statement it will be always at least 2.
You see that the inner transaction is reusing the outer transaction in case one exists. So if you really need the information of @@TRANCOUNT, you should think about putting that in a local variable first.
The script of the picture below can be downloaded here.