Identity Insert

  • Ken Wymore

    SSCoach

    Points: 16668

    Easy syntax question. Thanks!

  • SQLRNNR

    SSC Guru

    Points: 281252

    Thanks for the ?

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Mike Dougherty-384281

    SSCrazy

    Points: 2764

    "a table which has one column with identity property"

    I read the question incorrectly as "a table with one column additional to the identity column"

    it's not an excuse; just a possible explanation for the distribution of wrong answers on this question.

  • Rob Schripsema

    SSCertifiable

    Points: 7469

    Good question. Thanks.

    Rob Schripsema
    Propack, Inc.

  • Miles Neale

    SSChampion

    Points: 13147

    Thanks for the easy one to end the week!

    Not all gray hairs are Dinosaurs!

  • Primo Dang

    SSCrazy

    Points: 2643

    Mike Dougherty-384281 (5/3/2013)


    "a table which has one column with identity property"

    I read the question incorrectly as "a table with one column additional to the identity column"

    it's not an excuse; just a possible explanation for the distribution of wrong answers on this question.

    I disagree with that explanation, because I think the create table statement made it pretty clear that the identity column was the only one.

    I almost picked the wrong answer because I wasn't sure if using DEFAULT inside the VALUES argument would work as well as the DEFAULT VALUES argument, since it works perfectly for non-identity columns, like so:

    declare @a table (a int default 0, b int)

    insert @a values (default, 1)

    But then I remembered that trying to explicitly insert anything in an identity column (with IDENTITY_INSERT OFF for that table, of course) generates an error, even if it's DEFAULT (also, if that worked there would be two correct answers and no checkboxes ;-)).

    Edit: After rereading and a bit of testing I see your point, Mike. I didn't realize you could use DEFAULT inside VALUES for a table that contains an identity column, as long as you don't include the identity column in the VALUES list. For instance, this works just fine:

    declare @a table (a int default 0, b int, c int identity)

    insert @a values (default, 1)

  • TomThomson

    SSC Guru

    Points: 104773

    Nice question.

    There are two (harmless) oddities, though:

    I didn't (and still don't) know what the question is actually supposed to mean. "without changing the identity property of the column" can't really mean anything since there's no way of changing the identity property property while inserting values - the only way you can do that is drop and recreate the column, which doesn't insert values. But it's easy to get the answer because only one of the three answer options inserts values, and I just treated the question as meaning "which of these inserts will insert any values?" Alternatively, it could mean "what's the valid syntax for inserting the default value into the identity column without setting identity_insert on for the table?", as if the identity_insert state were a part of the identity property of the column rather than a property of the table. Given that I can't think of an possible meaning other than those two, and each results in the same answer, it maybe doesn't matter what the question actually meant.

    The reference is malformed (as a relative link instead of an absolute one) so that it points to a non-existent page withing sqlservercentral.com instead of to the technet or msdn page. And the explanation doesn't explain. But that's something that shouldn't stop anyone getting to the intended page, so although it's untidy and potentially irritating to anyone who clicks on it it doesn't really matter.

    Tom

  • Lokesh Vij

    SSChampion

    Points: 10836

    Good one!

    I remember posting a similar Qotd last year. Here is the link: http://www.sqlservercentral.com/questions/T-SQL/93222/[/url]

    ~ Lokesh Vij


    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • malleswarareddy_m

    SSCertifiable

    Points: 5847

    Lokesh Vij (5/4/2013)


    Good one!

    I remember posting a similar Qotd last year. Here is the link: http://www.sqlservercentral.com/questions/T-SQL/93222/[/url]

    +1

    Malleswarareddy
    I.T.Analyst
    MCITP(70-451)

  • Bangla

    Hall of Fame

    Points: 3137

    Nice one....

  • sqlnaive

    SSCoach

    Points: 17435

    Easy question. 🙂

  • aochss

    SSCommitted

    Points: 1677

    tom.w.brannon (5/3/2013)


    I've learned to read the discussions about all the questions because I learn things there as well. Did not know about adding a number to GO to get multiple executions. THANKS!

    Tom,

    WOW...This was a real eye opener...Filed under "I didn't know that"...I read your post and kept reading and your comment finally hit me about 3 posts later... Cool...

    Anton

Viewing 12 posts - 16 through 27 (of 27 total)

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