SQL teaser @@TRANCOUNT 2

[SQLDenis]

without running this what will the value of @@TRANCOUNT for the second print statement be?

SET ANSI_DEFAULTS ON 
GO 


PRINT @@TRANCOUNT 
GO 


BEGIN TRANSACTION 

--what will be the value of this trancount?
PRINT 'And the value  ==  ' + ltrim(str(@@TRANCOUNT))
GO 

rollback
GO

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[onpnt]

Spoiler

2

BOL

The BEGIN TRANSACTION statement increments @@TRANCOUNT by 1. ROLLBACK TRANSACTION decrements @@TRANCOUNT to 0, except for ROLLBACK TRANSACTION savepoint_name, which does not affect @@TRANCOUNT. COMMIT TRANSACTION or COMMIT WORK decrement @@TRANCOUNT by 1

And can everyone say what it will be without the "BEGIN TRANSACTION" ???

_{____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood}

 

[onpnt]

>>And can everyone say what it will be without the "BEGIN TRANSACTION"

and rollback of course sense you can't do that

_{____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood}

 

[SQLDenis]

your answer is correct however your reason is not

there is only one begin tran command how do you get your answer?

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[sunila7]

Because there is an Implicit Transaction running?

Sunil

 

[ESquared]

Spoiler

[tt]SET ANSI_DEFAULTS { ON | OFF }

When enabled (ON), this option enables the following SQL-92 settings:

SET ANSI_NULLS
SET CURSOR_CLOSE_ON_COMMIT
SET ANSI_NULL_DFLT_ON
SET IMPLICIT_TRANSACTIONS
SET ANSI_PADDING
SET QUOTED_IDENTIFIER
SET ANSI_WARNINGS[/tt]

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[SQLDenis]

yep that is true, now why does it skip a number

0 --> 2
instead of 1 --> 2

for example run this

SET ANSI_DEFAULTS ON 
GO 

create table #t(id int)
update #t set id =0

PRINT @@TRANCOUNT 
GO 


BEGIN TRANSACTION 

--what will be the value of this trancount?
PRINT 'And the value  ==  ' + ltrim(str(@@TRANCOUNT))
GO 

rollback
GO

and the values are 1 and 2

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[AlexCuse]

Spoiler

I'm late to the party, but isn't the PRINT statement treated as an implicit transaction?

Ignorance of certain subjects is a great part of wisdom

 

[SQLDenis]

Alex look at the answer ESquared gave

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[AlexCuse]

Right, didn't realize you cross-posted ;-)

Ignorance of certain subjects is a great part of wisdom

 

[ESquared]

>yep that is true, now why does it skip a number

Spoiler

PRINT is not a logged operation, that is, it performs no changes to the database which are candidates for committing or rolling back. Thus, it does not begin an implicit transaction.

[tt]SET IMPLICIT_TRANSACTIONS { ON | OFF }

When a connection is in implicit transaction mode and the connection is not currently in a transaction, executing any of the following statements starts a transaction:

ALTER TABLE
FETCH
REVOKE
CREATE
GRANT
SELECT
DELETE
INSERT
TRUNCATE TABLE
DROP
OPEN
UPDATE[/tt]

Evidently BEGIN TRANSACTION is also a statement which qualifies to begin an implicit transaction along with the explicit one it commands.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[ESquared]

I see that I ordered my lists wrong! Supposed to go horizontally, then vertically. Heh.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]