SQL Teaser Count That Trancount

BRAVO!

Interesting :-)

My answer was going to be only if you do a BEGIN TRAN before the SELECT @TRANCOUNT = @@TRANCOUNT and then it would be 1 and 1.

I tried it and I was partly right. I was anticipating a 0 in the VALUES column without the BEGIN TRAN. But I was expecting a 1 in the TranCountPassedIn column. I am still a bit puzzled there (it's been a long day ;-) )

So I was expecting 1 and 1 with a BEGIN TRAN (Or implicit Transactions on) or a 1 and 0 without Explicit Transactions on or doing a BEGIN TRAN.

Thanks for sharing this brain teaser... I did not expect the answer to be two for the default.  If you pass @@trancount directly into the insert the values end up being equal, and they do go up and down based on the number of begin tran/commit trans you have.

Notice that you get the same default value in the table with or without your first explicit transaction.

CREATE TABLE #Tran (id int, TranCountDefault int DEFAULT (@@TranCount),TranCountPassedIn int)

DECLARE @trancount int

SELECT @trancount = @@TranCount

INSERT #Tran (id, TranCountPassedIn) VALUES (1, @trancount)

BEGIN TRAN

SELECT @trancount = @@TranCount

INSERT #Tran (id, TranCountPassedIn) VALUES (2, @trancount)

 BEGIN TRAN

 SELECT @trancount = @@TranCount

 INSERT #Tran (id, TranCountPassedIn) VALUES (3, @trancount)

  BEGIN TRAN

  SELECT @trancount = @@TranCount

  INSERT #Tran (id, TranCountPassedIn) VALUES (4, @trancount)

  INSERT #Tran (id, TranCountPassedIn) VALUES (5, @@TranCount)

  COMMIT TRAN

 INSERT #Tran (id, TranCountPassedIn) VALUES (6, @@TranCount)

 COMMIT TRAN

INSERT #Tran (id, TranCountPassedIn) VALUES (7, @@TranCount)

COMMIT TRAN

INSERT #Tran (id, TranCountPassedIn) VALUES (8,@@TranCount)

SELECT * FROM #Tran

Wow, zero is obvious but I expected (1, 0) - where does 2 come from?

a

Nice answer