Post reply

Fun with IDENTITY - Part I

  • 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.

  • September 30, 2010 at 6:28 am

    #1229556

    edit - replied to the wrong topic!

  • October 1, 2010 at 9:24 am

    #1230309

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

  • 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

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply