IDENTITY INSERT

  • Ron McCullough

    SSC Guru

    Points: 63877

    Unpleasantly surprised at the high number of incorrect answers:

    Correct answers: 49% (196)

    Incorrect answers: 51% (207)

    Total attempts: 403

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Mike Dougherty-384281

    SSCrazy

    Points: 2764

    I'm not sure why anyone would think/talk about identity_insert this way. I understood this command was useful in the hopefully rare case where rows are restored to a table where the identity values of the row must be preserved. Under normal circumstances it shouldn't be turned off and under abnormal circumstances it should be used with great care and only by someone who knows exactly what they are doing.

    Even this http://msdn.microsoft.com/en-us/library/ms188059.aspx talks about filling "gaps" in identity values. This overly-trivial example implies that it is a good practice to arbitrarily re-use identity values. In that example if any other system has ever held a foreign key reference to id=3 and is expecting a relationship with a saw it will be very wrong to find a garden shovel. max(pkid) should not be expected or required to be equal to count() for a table.

    Can you imagine if social security numbers (another kind of identity value) were reissued this way? If your son or daughter is issued a driver's license number that was 'recycled' from someone with outstanding traffic violations that are now your problem?

    I don't know a more succinct way to express how recycled identity values is a Bad Idea.

  • Toreador

    SSChampion

    Points: 11261

    bitbucket-25253 (9/27/2012)


    Unpleasantly surprised at the high number of incorrect answers:

    I got it wrong, because I've never used identity_insert, so assumed the syntax was correct.

    I'm not at all surprised that there are so many others who made the same mistake - how many people have ever been in a situation where there's a need for this option?

  • Sean Lange

    SSC Guru

    Points: 286536

    I found this one a bit off. I assumed that the two lines setting IDENTITY_INSERT were typos because otherwise this entire batch would insert no rows. However if you fix the two syntactically incorrect lines there are two of those queries that would insert data. So then I tried to answer given the exact code in between the comments for each of the queries and there was only one obvious choice. I found myself wondering if the syntax error was intentional or not so the only logical answer left was query #3 because it didn't have the syntax error. A good question but it was a little unclear.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Ken Wymore

    SSCoach

    Points: 16642

    I almost chose answer 2 but upon re-reading it realized that the identity insert syntax was wrong. I never use INSERT INTO <table> DEFAULT VALUES but still got it through process of elimination. Thanks for the question Lokesh!

  • asifkareem

    Ten Centuries

    Points: 1089

    Good Question, i used in one of my Task, its interesting Topic,

    Thanks Lokesh.

  • Error Handler

    Ten Centuries

    Points: 1086

    Nice question!

    Best,
    Naseer Ahmad
    SQL Server DBA

  • Divine Flame

    SSCoach

    Points: 15941

    I am not sure why the question is being criticized. For me, it was a straight forward question. The question asks which query will insert the data in the table successfully?, and there was only one correct answer for that. Rest all options were wrong due to syntax errors etc. Just my opinion.


    Sujeet Singh

  • Andre Guerreiro

    SSCertifiable

    Points: 7319

    Sean Lange (9/27/2012)


    I found this one a bit off. I assumed that the two lines setting IDENTITY_INSERT were typos because otherwise this entire batch would insert no rows. However if you fix the two syntactically incorrect lines there are two of those queries that would insert data. So then I tried to answer given the exact code in between the comments for each of the queries and there was only one obvious choice. I found myself wondering if the syntax error was intentional or not so the only logical answer left was query #3 because it didn't have the syntax error. A good question but it was a little unclear.

    Exactly my thoughts.

    I wasn't sure if it the table name was omitted because of a typo.

    I'm not a human T-SQL parser. 😀

    I thought the concept was being discussed not they syntax.

    Anyway I learned something new because I didn't know INSERT #3 would work.

    Best regards,

    Andre Guerreiro Neto

    Database Analyst
    http://www.softplan.com.br
    MCITPx1/MCTSx2/MCSE/MCSA

  • Artur Komkov

    Mr or Mrs. 500

    Points: 519

    Nice question but inserts suppose to be separated by "GO". Otherwise no one would be performed.

  • Koen Verbeeck

    SSC Guru

    Points: 258975

    Easy one, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • TheGreenShepherd

    SSCertifiable

    Points: 6979

    This is why people don't like DBA's.

  • Rose Bud

    SSCrazy

    Points: 2971

    sknox (9/27/2012)


    1. Query 1 fails because there are no values provided to INSERT.

    2. Query 2 fails because the syntax for SET IDENTITY_INSERT requires a table name.

    3. Query 3 succeeds because DEFAULT VALUES instructs SQL Server to insert the identity vale.

    4. Query 4 fails because VALUES (DEFAULT) does not work for identity columns.

    The answer should have included a reference to INSERT (http://msdn.microsoft.com/en-us/library/aa933206(v=sql.80).aspx) for completeness to explain why 1 and 4 fail while 3 succeeds.

    Otherwise, a pretty good question.

    Thanks sknox! I needed this explanation spelled out.

  • TomThomson

    SSC Guru

    Points: 104773

    Nice question - interesting collection of syntax errors.

    Tom

  • TomThomson

    SSC Guru

    Points: 104773

    bitbucket-25253 (9/27/2012)


    Unpleasantly surprised at the high number of incorrect answers:

    Correct answers: 49% (196)

    Incorrect answers: 51% (207)

    Total attempts: 403

    It's got a tlittle bit better:

    Correct answers: 50% (524)

    Incorrect answers: 50% (519)

    But that's still pretty awful, isn't it?

    I can understand some people falling for option 2 because they have never used or learned about identity insert so they don't spot the syntax error (or because they look at it too quickly and don't notice it), but how can anyone think 1 or 4 would be valid?

    Tom

Viewing 15 posts - 16 through 30 (of 33 total)

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