IDENTITY INSERT

Question

Post reply

IDENTITY INSERT

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

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

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: 2794 More actions · Answer 2

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: 11365 More actions · Answer 3

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: 286590 More actions · Answer 4

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 SSCoach Points: 17191 More actions · Answer 5

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 More actions · Answer 6

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

Thanks Lokesh.

Error Handler Ten Centuries Points: 1086 More actions · Answer 7

Error Handler

Ten Centuries

Points: 1086

September 27, 2012 at 8:09 am

#1542918

Nice question!

Best,
Naseer Ahmad
SQL Server DBA

Divine Flame SSCoach Points: 15943 More actions · Answer 8

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 More actions · Answer 9

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 More actions · Answer 10

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 11

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 More actions · Answer 12

TheGreenShepherd

SSCertifiable

Points: 6979

September 28, 2012 at 11:23 am

#1543559

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

Rose Bud Hall of Fame Points: 3147 More actions · Answer 13

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 More actions · Answer 14

Nice question - interesting collection of syntax errors.

Tom

TomThomson SSC Guru Points: 104773 More actions · Answer 15

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