Insert without a PK

Question

Insert without a PK

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

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

Mauricio_ SSCrazy Points: 2935 More actions · Answer 1

Louis Hillebrand (6/10/2015)
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)

Same here. It was hard to understand why indexes are disabled according to the question.

Mauricio_ SSCrazy Points: 2935 More actions · Answer 2

I wanted to have all green checks in my QOTD list and now I've got a red cross!

edwardwill SSCertifiable Points: 5856 More actions · Answer 3

The question was wrong. My answer was most nearly correct (one row inserted, one row failed with error).

aron.vandermeyden SSC Veteran Points: 206 More actions · Answer 4

The deployment of this QoTD should have been automated so what worked in test would have worked in production 🙂

Hany Helmy SSChampion Points: 13921 More actions · Answer 5

Mauricio_ (6/10/2015)
I wanted to have all green checks in my QOTD list and now I've got a red cross!

You will get used to this 😛

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

Hany Helmy SSChampion Points: 13921 More actions · Answer 6

Now we all agree that the 1st row will be inserted & there is absolutely nothing in the code will disable the indexes, so the next question is: "When we will get our points back?!" 😉

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

Mauricio_ SSCrazy Points: 2935 More actions · Answer 7

Hany Helmy (6/10/2015)
Now we all agree that the 1st row will be inserted & there is absolutely nothing in the code will disable the indexes, so the next question is: "When we will get our points back?!" 😉

One of these days (Pink Floyd)

R M Buda SSCrazy Points: 2225 More actions · Answer 8

CREATE TABLE SalesArchive

(

Salesid INT PRIMARY KEY NONCLUSTERED

, SalesPersonID INT

, SaleDate DATETIME2

, SaleTotal NUMERIC(12, 4)

);

GO

CREATE CLUSTERED INDEX SalesArchive_CI_SalesPersonID ON dbo.SalesArchive(SalesPersonID) ;

GO

Yep: qustion definitely needs adjusting:

INSERT dbo.SalesArchive

( Salesid

, SalesPersonID

, SaleDate

, SaleTotal

)

VALUES

( 1, 1, GETDATE(), 25.10 );

INSERT dbo.SalesArchive

( Salesid

, SalesPersonID

, SaleDate

, SaleTotal

)

VALUES ( 1, 1, GETDATE(), 25.12 );

--------------------------------------------------------------------

Msg 2627, Level 14, State 1, Line 20

Violation of PRIMARY KEY constraint 'PK__SalesArc__C953FF0B10B89DF3'. Cannot insert duplicate key in object 'dbo.SalesArchive'. The duplicate key value is (1).

The statement has been terminated.

dale_berta Ten Centuries Points: 1328 More actions · Answer 9

I didn't see a correct answer. The table is created with PK, which implies an index. A CLUSTERED index is created. Both indexes are active. The first insert works (why wouldn't it?). The second insert fails because of duplicate key error on PK.

When I didn't see a correct answer, I looked at T-SQL reference; no help. Then I actually ran the code. It works as I described above. Table with two live indexes, and one row gets inserted. Is this version dependent? I'm on 2008 R2.

Ed Wagner SSC Guru Points: 287026 More actions · Answer 10

I didn't see the disable command either, so I had to run it and answer based on behavior. Like everyone else, it was wrong because the question was incomplete.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Junior Galvão - MVP SSCertifiable Points: 6951 More actions · Answer 11

Hi Steve,

I know we can't violate the primary key, but I ran this script in SQL Server and SQL Server SP2 2012 2014 SP1 and the first insert was successfully processed, the second coming on primary key violation error.

I believe that the correct answer is the alternative 2.

karthik babu Hall of Fame Points: 3335 More actions · Answer 12

Not sure if this set of code behaves same in all of the 2005+ versions.. Steve has to confirm what went wrong!!!! 😀

My points please!!! :hehe:

______________________________________________________________Every Problem has a Solution; Every Solution has a Problem: 🙂

Xavon SSCrazy Points: 2280 More actions · Answer 13

Yeah, I did it. I figured out the author's intent instead of the proper literal interpretation of the question.

I guess it helps that this is a follow-up to a question from 2 days again.

JaimePC SSC Eights! Points: 864 More actions · Answer 14

Agree!

i don't see how this exercise have any concern with the answer. :unsure:

jduhaney SSC Veteran Points: 265 More actions · Answer 15

I too did not see a statement to disable the index, and did not see a correct answer. I just chose one so I could see the results and discussion :satisfied: