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

Summer SQL Teaser #9 ROLLBACK

This one is not so much a teaser but it will show you what you can do in case you want to insert data in a logging table after a rollback occurs
Without running this try to guess what the counts of the three tables will be after the rollback

CREATE TABLE Test (id int)
CREATE TABLE #Test (id int)
DECLARE @Test table (id int)


BEGIN TRAN
   INSERT INTO
@Test VALUES(1)
   INSERT INTO Test VALUES(1)
   INSERT INTO #Test VALUES(1)
ROLLBACK TRAN


SELECT
'@test',COUNT(*) FROM @Test
SELECT ' test',COUNT(*) FROM Test
SELECT '#test',COUNT(*) FROM #Test


DROP TABLE Test,#Test
Published Friday, July 27, 2007 10:01 AM 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

 

Macie said:

Without running it:

1, 0, 0

@tables can be quite useful for saving data through a rollback.

July 27, 2007 11:44 AM
 

Scott said:

Nice tip.

August 1, 2007 7:59 AM
 

Avinash Goyal said:

This teaser has been quite useful in one of my assignment.

Thanks,

Avinash

March 27, 2008 8:36 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