Insert without a PK

Question

Insert without a PK

Viewing 15 posts - 31 through 45 (of 61 total)

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

Mike Hays SSCommitted Points: 1871 More actions · Answer 1

Oppps - I think we need to wait to MST (Mountain Standard Time) gets to work to find out what happened.

g.britton SSChampion Points: 13863 More actions · Answer 2

OK -- so the question is clearly missing something. Here's what I tried: Create the table as given, then disable the PK:

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

ALTER INDEX PK__SalesArc__C953FF0BED0DD138 ON [dbo].[SalesArchive] DISABLE;

GO

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 );

Result:

(1 row(s) affected)

Now, truncate the table, disable the clustered index as well and try again:

TRUNCATE TABLE [dbo].[SalesArchive];

GO

ALTER INDEX SalesArchive_CI_SalesPersonID ON [dbo].[SalesArchive] DISABLE;

GO

INSERT ...

Result:

Msg 8655, Level 16, State 1, Line 2

The query processor is unable to produce a plan because the index 'SalesArchive_CI_SalesPersonID' on table or view 'SalesArchive' is disabled.

However, since we can't tell what Steve really meant, it is really not possible to answer the question properly.

Gerald Britton, Pluralsight courses

Nevyn SSChampion Points: 14210 More actions · Answer 3

Steve Jones - SSC Editor (6/9/2015)
Comments posted to this topic are about the item <A HREF="/questions/T-SQL/126632/">Insert without a PK</A>

Yeah, um, i still may have been wrong, but if the question had been complete it would have helped.

Bobby Russell SSCrazy Points: 2801 More actions · Answer 4

1st answer WAS the correct answer. I tested it after I got it wrong. There is nothing in any of this about disabled indexes. Are you missing some code?

2 points please.

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 5

I could successfully able to Insert a record which had a "saleTotal" of 25.1000.

Thanks.

Tom_Sacramento SSCertifiable Points: 6221 More actions · Answer 6

On my system the first insert worked,. the second fail for constraint violation and neither index was disabled... what gives Steve?

Tom in Sacramento - For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/

Jeff Moden SSC Guru Points: 1003896 More actions · Answer 7

Bobby Russell (6/10/2015)
1st answer WAS the correct answer. I tested it after I got it wrong. There is nothing in any of this about disabled indexes. Are you missing some code?
2 points please.

It's not the correct answer. The 1st script says that you get an error on the first script. As they are currently written, none of the answers are correct.

--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)

abjames Old Hand Points: 339 More actions · Answer 8

I'm confused. When I ran these scripts, I got one row inserted and the second insert threw an error caused by a duplicate key.

RK Mandava SSCarpal Tunnel Points: 4153 More actions · Answer 9

Mike Hays (6/10/2015)
Oppps - I think we need to wait to MST (Mountain Standard Time) gets to work to find out what happened.

+1

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

RK Mandava (6/10/2015)
Mike Hays (6/10/2015)
Oppps - I think we need to wait to MST (Mountain Standard Time) gets to work to find out what happened.
+1

Except on on Tuesdays. 😛

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

Ken SSCoach Points: 17191 More actions · Answer 11

Looks like I should have just closed out the window instead of trying to answer this one since there was obviously something missing from the script. I imagine we will see points awarded back tomorrow.

JChrisCompton SSCommitted Points: 1609 More actions · Answer 12

Maybe there was a line missing about disable?

For me (version 10.50.6000) the first was inserted and the second failed with the message:

Msg 2627, Level 14, State 1, Line 17

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

The statement has been terminated.

Steve Jones - SSC Editor SSC Guru Points: 734564 More actions · Answer 13

Apologies. The disable statement is missing in the code.

Points awarded back and the question corrected.

J DBA SSCertifiable Points: 5008 More actions · Answer 14

I agree the question as written ~~has~~ had no correct answers.

I approached this like I do issues in my systems where sometimes you don't have all the information as to why code was written a certain way or why something is acting weird.

The key to getting this one right is the clue in the Question Title "Insert without a PK". Couple that with Steve's other Cluster key disable question, I surmised that Steve intended to disable the Cluster Key, thus disabling the Non-Clustered PK as well.

With that information the "...query processor..." answer was the best option.

It also helps that I have a crystal ball that told me the correct answer. 😀

J DBA

abjames Old Hand Points: 339 More actions · Answer 15

I used my Magic 8 Ball and it wasn't anywhere near as helpful.