THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Denis Gobo

SQL Teaser Count That Trancount

Today's teaser is very simple

First create this table

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


As you can see that table has two columns, one column has a default of @@TRANCOUNT. Now run this piece of code

DECLARE @trancount int

SELECT @trancount = @@TranCount

INSERT #Tran (TranCountPassedIn) VALUES (@trancount)

We assigned @@TRANCOUNT to the @trancount variable and we passed that in, so TranCountDefault  has the default value and TranCountPassedIn has the value we passed in

Now when you do a select from the table will the 2 columns have the same value?


Published Thursday, February 26, 2009 5:15 PM by Denis Gobo
Filed under:



Alexander Kuznetsov said:


February 26, 2009 5:50 PM

Mike Walsh said:

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.

February 26, 2009 8:45 PM

Jeff said:

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)


SELECT @trancount = @@TranCount

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


 SELECT @trancount = @@TranCount

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


  SELECT @trancount = @@TranCount

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

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


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


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


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


March 3, 2009 3:14 PM

Michael Zilberstein said:

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

March 8, 2009 8:24 AM

a said:


May 14, 2010 5:34 AM

Micheal Jackson said:

Nice answer

May 14, 2010 5:35 AM
New Comments to this post are disabled

About Denis Gobo

I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-( I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b. Some of you might know me from or even from some of the newsgroups where I go by the name Denis the SQL Menace If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.

This Blog


Privacy Statement