September 9, 2010 at 9:32 pm
Comments posted to this topic are about the item Disabling Indexes
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins
September 9, 2010 at 10:47 pm
Nice question, thanks!
September 9, 2010 at 11:17 pm
"ALTER INDEX PK_MyId ON MyTable DISABLE" at this line my brain stopped.
Nice question. Though i think it would have been better if i had stopped at the row above. Since if one didnt know that disabling the clustered index made it impossible to select from the table. You would have selected the 2 row answer anyway.
September 9, 2010 at 11:28 pm
This was removed by the editor as SPAM
September 10, 2010 at 12:59 am
The question would be more educational if it had no 'DROP CONSTRAINT' statement. In this case I would probably have answered wrong 🙂
September 10, 2010 at 2:42 am
i have checked the same on sql server 2005 on windows server 2003 but i am getting the error message
The query processor is unable to produce a plan because the index 'PK_MyId' on table or view 'MyTable' is disabled.
i think you should check it again.
September 10, 2010 at 3:34 am
nice one thanks...
September 10, 2010 at 3:35 am
This works on sql 2005... plz. check... you would have commented this line
ALTER TABLE MyTable DROP CONSTRAINT PK_MyId
mukeshkane (9/10/2010)
i have checked the same on sql server 2005 on windows server 2003 but i am getting the error messageThe query processor is unable to produce a plan because the index 'PK_MyId' on table or view 'MyTable' is disabled.
i think you should check it again.
September 10, 2010 at 3:40 am
Fascinating. Thanks
September 10, 2010 at 5:25 am
still getting the same error message
September 10, 2010 at 5:29 am
mukeshkane (9/10/2010)
still getting the same error message
Could you copy and paste the exact T-SQL code you are using? There must be something wrong there. I can run the code on my SQL Server 2005 test server, and I get the two rows returned.
September 10, 2010 at 5:32 am
-- Create Table
CREATE TABLE MyTable (MyId INT IDENTITY (1,1),
MyCity NVARCHAR(50),
CONSTRAINT PK_MyId PRIMARY KEY CLUSTERED (MyId))
-- Populate Dummy data
INSERT INTO MyTable VALUES ('Boston')
INSERT INTO MyTable VALUES ('New Delhi')
-- Disable Clustered Index
ALTER INDEX PK_MyId ON MyTable DISABLE
GO
-- Drop the associated constraints
ALTER TABLE MyTable DROP CONSTRAINT PK_MyIdGO
-- Check
SELECT * FROM MyTable
-- Cleanup!
DROP TABLE MyTable
September 10, 2010 at 5:55 am
the Go on this line
ALTER TABLE MyTable DROP CONSTRAINT PK_MyIdGO
needs to be on the following line
ALTER TABLE MyTable DROP CONSTRAINT PK_MyId
GO
September 10, 2010 at 6:04 am
mukeshkane (9/10/2010)
-- Create TableCREATE TABLE MyTable (MyId INT IDENTITY (1,1),
MyCity NVARCHAR(50),
CONSTRAINT PK_MyId PRIMARY KEY CLUSTERED (MyId))
-- Populate Dummy data
INSERT INTO MyTable VALUES ('Boston')
INSERT INTO MyTable VALUES ('New Delhi')
-- Disable Clustered Index
ALTER INDEX PK_MyId ON MyTable DISABLE
GO
-- Drop the associated constraints
ALTER TABLE MyTable DROP CONSTRAINT PK_MyIdGO
-- Check
SELECT * FROM MyTable
-- Cleanup!
DROP TABLE MyTable
The devil is in the details!
You are missing a "GO" between the ALTER TABLE DROP CONSTRAINT and the SELECT. The GO is a batch seperator, and SQL Server compiles entire batches at a time. Without the GO, SQL Server tries to compile both the ALTER TABLE, the SELECT, and the DROP TABLE before starting execution. But because at that time, the constraint exists in disabled mode, the SELECT won't even compile.
Add the GO. Now the ALTER TABLE DROP CONSTRAINT is in its own batch; it will be compiled and executed. The next batch (with the SELECT) will only be compiled and executed after the constraint has been dropped.
EDIT: I had not noticed that the missing GO was moved to the end of the previous line (thanks for catching that, David!) Surprising that the reported error was not for trying to drop a non-existing constraint. (But that is probably because this is a run-time error, not a compile-time error).
September 10, 2010 at 6:12 am
I am now running the following line
-- Create Table
CREATE TABLE MyTable (MyId INT IDENTITY (1,1),
MyCity NVARCHAR(50),
CONSTRAINT PK_MyId PRIMARY KEY CLUSTERED (MyId))
-- Populate Dummy data
INSERT INTO MyTable VALUES ('Boston')
INSERT INTO MyTable VALUES ('New Delhi')
-- Disable Clustered Index
ALTER INDEX PK_MyId ON MyTable DISABLE
-- Drop the associated constraints
ALTER TABLE MyTable DROP CONSTRAINT PK_MyIdGO
GO
-- Check
SELECT * FROM MyTable
-- Cleanup!
DROP TABLE MyTable
I am getting the following output
(1 row(s) affected)
(1 row(s) affected)
Msg 3728, Level 16, State 1, Line 11
'PK_MyIdGO' is not a constraint.
Msg 3727, Level 16, State 0, Line 11
Could not drop constraint. See previous errors.
Msg 8655, Level 16, State 1, Line 2
The query processor is unable to produce a plan because the index 'PK_MyId' on table or view 'MyTable' is disabled.
Is I am still missing something??
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply