Insert without a PK

Question

Insert without a PK

Steve Jones - SSC Editor

SSC Guru

Points: 736183
More actions
June 9, 2015 at 9:37 pm

#100909

Comments posted to this topic are about the item Insert without a PK

Viewing 15 posts - 1 through 15 (of 61 total)

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

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 1

I have to say that this is one of the weirdest set of answers I've ever seen. This is the reason why I don't take certification tests. 😛

I certainly don't see anything that would disable the indexes in any of the scripts.

Maybe it's just me or the caffeine is wearing off for the day but I don't believe there's actually a correct answer posted as they are currently written.

If the word "NOT" was included before the word "disabled" in the second answer, it would be correct if n changes were made to the code. Like I said, either something is missing in the code or the answers are out in left field.

In the meantime, I'm not going to try to answer this question. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

leif-1044647 SSC Journeyman Points: 90 More actions · Answer 2

Agree, cant say i found any logic in answers vs question script 🙂

Louis Hillebrand SSCarpal Tunnel Points: 4982 More actions · Answer 3

After running the first script the table has 2 indexes, A unique non-clustered on SalesId (PRIMARY KEY) and a clustered on SalesId.

The first insert inserts one row, the second fail on the PK index.

As all the answers are not totally correct, I took what looked to me as the most correct (the second insert produces a PK violation).

My 2 points please...

Louis.

(SQL-Version : 2008 R2)

Mighty SSCrazy Eights Points: 9063 More actions · Answer 4

For me the first insert worked, and the second failed with a constraint error. However my indexes were not disabled.

So I choose the most matching answer, but that was incorrect. I still think it was more correct than the "correct answer".

Am I missing something?!

Neil Burton SSC-Insane Points: 22999 More actions · Answer 5

As the others have said, I ran the scripts and, as expected, I got an error about the violation of the primary key constraint. I couldn't see anything about disabling indexes in the question so I went with the answer I'd found to be most right which turned out to be wrong.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

Mick Fitz Hall of Fame Points: 3220 More actions · Answer 6

As the others have said, I ran the scripts and, as expected, I got an error about the violation of the primary key constraint. I couldn't see anything about disabling indexes in the question so I went with the answer I'd found to be most right which is marked as wrong.

radek.hruby SSCrazy Points: 2123 More actions · Answer 7

In my personal opinion there is missing that part that should disable the PK ... author probably mentioned to put it before those two insert however he didn't.

Carlo Romagnano SSC-Insane Points: 22668 More actions · Answer 8

I thought that this statement was missing:

alter INDEX SalesArchive_CI_SalesPersonID ON dbo.SalesArchive disable

... and got it right!

😀

Nicholas Cross Mr or Mrs. 500 Points: 543 More actions · Answer 9

There is nothing in the question that suggests the PK is disabled, so I couldn't see any sensible answer. Went with the one where the first insert succeeds and the second fails. The question needs reviewing. :ermm:

Toreador SSChampion Points: 11365 More actions · Answer 10

The answers only make any sense if there is a missing line that disables the primary key, so I answered on this assumption and got it right 🙂

Unfortunately, everyone else who got it wrong will eventually be given points anyway when the question is corrected!

Magnus Ahlkvist Ten Centuries Points: 1000 More actions · Answer 11

The QotD from June 8 includes the disabling of indexes. That part of the code was not pasted into this QotD, so like many already mentioned, neither of the answers are correct.

Sql Server blog: http://www.tsql.nu

sipas Hall of Fame Points: 3828 More actions · Answer 12

None of them are correct, but 1, 2 and 4 are either the least incorrect or could be correct if you assume something's missing in the question. But I don't think anyone who chose 'Both rows are inserted' should get any points back(!?).

andrew_dale Ten Centuries Points: 1280 More actions · Answer 13

Nicholas Cross (6/10/2015)
There is nothing in the question that suggests the PK is disabled, so I couldn't see any sensible answer. Went with the one where the first insert succeeds and the second fails. The question needs reviewing. :ermm:

+1

PurpleLady SSCertifiable Points: 7819 More actions · Answer 14

I know I haven't had my coffee yet but there's a problem with the scripts as shown 😉