Identity Insert

  • Easy syntax question. Thanks!

  • 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

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

  • Good question. Thanks.

    Rob Schripsema
    Propack, Inc.

  • Thanks for the easy one to end the week!

    Not all gray hairs are Dinosaurs!

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

  • 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

  • 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

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

  • Nice one....

  • Easy question. 🙂

  • 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