Assume I connect to SQL server 2008 via SQL Server Management Studio ( SSMS ) and open new window W1 by clicking on New Query tab and write the following inside W1:

BEGIN TRANSACTION;

If I execute this statement 5 times, and then write (inside W1)

SELECT @@TRANCOUNT;

, then the value returned will be 5. But if I open another window W2 ( inside the same SSMS instance and thus on the same connection ) and write inside W2

SELECT @@TRANCOUNT; 

then value returned will be 0.

@@TRANCOUNT variable returns the number of active transactions for the current connection.

Both W1 and W2 windows were opened on the same connection, so shouldn’t ( according to the above quote ) in both W1 and W2 variable @@TRANCOUNT hold the same value?

thanx