THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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?

SELECT * FROM #Tran

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

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Alexander Kuznetsov said:

BRAVO!

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)

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

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:

a

May 14, 2010 5:34 AM
 

Micheal Jackson said:

Nice answer

May 14, 2010 5:35 AM

Leave a Comment

(required) 
(required) 
Submit

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 http://sqlservercode.blogspot.com/ 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

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement