Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Fun with IDENTITY - Part I Expand / Collapse
Author
Message
Posted Wednesday, September 29, 2010 4:40 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:37 PM
Points: 21,215, Visits: 14,917
Thanks for the question.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #995065
Posted Wednesday, September 29, 2010 6:02 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 24, 2014 2:28 PM
Points: 1,393, Visits: 477
UMG Developer (9/28/2010)
I agree, nice question, but it would have been even better with a fourth option: 1-Boston;2-New Delhi

Without that option it was really a test of transactions, not of IDENTITY. (Maybe that was the point to through people off the scent?)


Agreed, reading the question I was thinking would New Delhi be 2 or 3. When 2 was not an option that answer was clear.
Post #995111
Posted Wednesday, September 29, 2010 6:06 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 9:47 AM
Points: 2,919, Visits: 2,525
Good question - after the fun with transactions, nobody should have gotten this one wrong

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
Post #995115
Posted Wednesday, September 29, 2010 8:17 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:56 AM
Points: 3,924, Visits: 1,607
Easy one. With Begin and Rollback Transaction included in question it's easy to select correct answer.

SQL DBA.
Post #995264
Posted Wednesday, September 29, 2010 8:23 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 24, 2014 8:41 AM
Points: 2,818, Visits: 2,553
It's nice to get a QoTD once in a while without a trick. Nice question.
Post #995277
Posted Wednesday, September 29, 2010 9:27 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, July 25, 2014 9:27 AM
Points: 649, Visits: 470
I agree, a forth option of 1-Boston;2-New Delhi would have made the question better. With a forth option, I would have had to think more about the impact of a rolled back transaction has on the Identity values to get the correct answer.

Good question though.



Post #995364
Posted Thursday, September 30, 2010 6:28 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:33 AM
Points: 1,713, Visits: 6,245
edit - replied to the wrong topic!
Post #995862
Posted Friday, October 1, 2010 9:24 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, January 10, 2014 9:03 AM
Points: 103, Visits: 289
Why would you ever use "rollback transaction" unless with an "if @@error" statement?
Post #996773
Posted Friday, October 1, 2010 11:28 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 4:25 AM
Points: 8,559, Visits: 9,053
The correct answer "It depends" isn't available as an option.

How many rows are produced depends on the IMPLICIT_TRANSACTIONS setting.

If ANSI_DEFAULTS or IMPLICIT_TRANSACTIONS have been set on, this code will produce no rows (just an error message for the third insert, which is an attempt to insert into a nonexistent table). The BEGIN TRAN statement won't open a transaction because there is already an active transaction.

But most connections default to autocommit mode ( so IMPLICIT_TRANSACTIONS is off) so the code will produce 2 rows, as in the supposedly correct answer.

As no rows wasn't an option, it was possible to deduce that IMPLICIT_TRANSACTIONS had to be OFF so that the 2-row answer was the one expected by the person who set the question.





Tom
Post #996872
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse