Fun with IDENTITY - Part I

• daveb87

  Ten Centuries

  Points: 1025

  More actions

  September 29, 2010 at 9:27 am

  #1229149

  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.

• Toreador

  SSChampion

  Points: 11379

  More actions

  September 30, 2010 at 6:28 am

  #1229556

  edit - replied to the wrong topic!

• Amy.G

  SSCrazy

  Points: 2305

  More actions

  October 1, 2010 at 9:24 am

  #1230309

  Why would you ever use "rollback transaction" unless with an "if @@error" statement?

• TomThomson

  SSC Guru

  Points: 104773

  More actions

  October 1, 2010 at 11:28 am

  #1230396

  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