June 10, 2015 at 11:46 am
It's so sad start the day with a "Sorry, yo where wrong" message when the expected message is "You got it rigth!". Thank you for bring me back my points Steve 😀
June 10, 2015 at 1:16 pm
Well, that explains why I got the question correct, I read it after it was fixed.
June 10, 2015 at 2:10 pm
Learned to not trust the Emailed question source today!
I've never seen the question change between the Email in the morning and the webpage later...
Should have suspected something wrong when there appeared to be no correct answers, confirmed by testing... :blush:
June 10, 2015 at 2:11 pm
The table has a primary key of SalesID.
The table also has a NONCLUSTERED index on SalesID.
The table has a CLUSTERED index on SalesPersonID.
Before attempting the insert, the clustered index is disabled.
Books Online states "Disabling a CLUSTERED INDEX prevents user access to the underlying table data."
So the issue isn't with the primary key (on SalesID), it's with the clustered index (on SalesPersonID).
So the answer should be "none of the above." - the statement, "Neither row is inserted as the QP cannot produce a plan with the PK disabled" is incorrect. 🙂
June 10, 2015 at 2:16 pm
phillyflats (6/10/2015)
The table has a primary key of SalesID.The table also has a NONCLUSTERED index on SalesID.
The table has a CLUSTERED index on SalesPersonID.
Before attempting the insert, the clustered index is disabled.
Books Online states "Disabling a CLUSTERED INDEX prevents user access to the underlying table data."
So the issue isn't with the primary key (on SalesID), it's with the clustered index (on SalesPersonID).
So the answer should be "none of the above." - the statement, "Neither row is inserted as the QP cannot produce a plan with the PK disabled" is incorrect. 🙂
Actually, if you run the code (copied below) you get a warning message warning you that the Primary Key was disabled as a result of disabling the clustered index (also shown below).
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 SalesArchive_CI_SalesPersonID ON dbo.SalesArchive DISABLE;
GO
Warning: Index 'PK__SalesArc__C953FF0B96946542' on table 'SalesArchive' was disabled as a result of disabling the clustered index on the table.
June 11, 2015 at 12:56 am
On my SQL Server 2014 it works as expected.
After the first script I got in the Message Pane:
CREATE_TABLE - dbo.SalesArchive
ALTER_EXTENDED_PROPERTY - .IGS2DB
CREATE_INDEX - dbo.SalesArchive_CI_SalesPersonID
ALTER_EXTENDED_PROPERTY - .IGS2DB
Warning: Index 'PK__SalesArc__C953FF0BD8FAAED2' on table 'SalesArchive' was disabled as a result of disabling the clustered index on the table.
ALTER_INDEX - dbo.SalesArchive_CI_SalesPersonID
ALTER_EXTENDED_PROPERTY - .IGS2DB
So then it is obvious that no data can be inserted.
June 11, 2015 at 1:44 am
After seeing question and answers yesterday, it was obvious that the script was incomplete. There was supposed to be an ALTER INDEX ... DISABLE in there - but for which of the two indexes? The question title suggested the supporting index for the primary key, but since this index was unnamed and hence hard to disable in a reproducable script, I didn't dare gamble on it.
Today the missing piece of the puzzle was added. Now it was obvious which of the answer options was intended to be chosen. However, it is still not totally correct - the reason that the inserts cannot be processed is that the clustered index is disabled, not the primary key. You will not get any error and have two rows inserted if you disable only the nonclustered index supporting the primary key (and hence, by extension, the primary key itself).
June 11, 2015 at 1:51 am
I agree with that Hugo.
So I understand that the question script has been changed.
(I missed the question yesterday so I didn't understand all the replies here)
June 11, 2015 at 9:52 am
<Insert the usual 'Got it wrong but learned something' here>
😉
June 11, 2015 at 10:27 am
Arrrgg, apologies. That's what I get for trying to fix things quickly.
Question corrected to disable the PK, and not the clustered index.
June 11, 2015 at 10:30 am
just the clustered index
June 11, 2015 at 11:47 am
I also didn't see anything about the PK being disabled so assumed that the insert would fail due to it being a duplicate PK attempt. Even reviewing the answer, I didn't see that the PK was disabled. Must have missed something.
June 11, 2015 at 2:15 pm
I looked at this early and decided to wait 26 hours and see what the question was meant to be before answering. Judging by comments, it's been "corrected" incorrectly at least once (I suspect twice) and then finally corrected to its current state, which led me to the answer which delivers the points (whether it's correct or not. :hehe:).
edit: Actually the question and answer are now correct (perhaps by accident :w00t:) but the explanation is not. :rolleyes::laugh:
Tom
June 11, 2015 at 10:13 pm
Steve Jones - SSC Editor (6/11/2015)
Arrrgg, apologies. That's what I get for trying to fix things quickly.Question corrected to disable the PK, and not the clustered index.
As the question stasnds at the moment the PK is clustered.
So disabling applied to both PK and clustered index. 🙂
If to make PK nonclustered, as it was initially, then both rows are inserted with no issues.
At least on my server.
Try it for yourself:
CREATE TABLE SalesArchive
(
Salesid int CONSTRAINT SalesArchive_PK 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 SalesArchive_PK ON dbo.SalesArchive DISABLE;
GO
--What happens when I do this?
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 );
GO
_____________
Code for TallyGenerator
June 12, 2015 at 12:13 am
Yes Sergiy,
If that was the original question, then you are right.
Now I have had also 2 rows inserted.
Viewing 15 posts - 46 through 60 (of 61 total)
You must be logged in to reply to this topic. Login to reply