Identity Insert

Question

Post reply

Identity Insert

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

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

Ken SSCoach Points: 17191 More actions · Answer 1

Ken

SSCoach

Points: 17191

May 3, 2013 at 8:16 am

#1612227

Easy syntax question. Thanks!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

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

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

Rob Schripsema

SSCertifiable

Points: 7469

May 3, 2013 at 8:44 am

#1612244

Good question. Thanks.

Rob Schripsema
Propack, Inc.

Miles Neale SSChampion Points: 13147 More actions · Answer 5

Thanks for the easy one to end the week!

Not all gray hairs are Dinosaurs!

Primo Dang SSCrazy Points: 2643 More actions · Answer 6

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

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

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

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

Bangla

Hall of Fame

Points: 3137

May 5, 2013 at 11:25 pm

#1612515

Nice one....

sqlnaive SSCoach Points: 17437 More actions · Answer 11

sqlnaive

SSCoach

Points: 17437

May 17, 2013 at 12:57 am

#1616061

Easy question. 🙂

aochss SSCommitted Points: 1677 More actions · Answer 12

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