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