Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Disabling Indexes Expand / Collapse
Author
Message
Posted Friday, September 10, 2010 5:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:59 AM
Points: 5,930, Visits: 8,181
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.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #983663
Posted Friday, September 10, 2010 5:32 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 1:19 AM
Points: 143, Visits: 45
-- 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
Post #983666
Posted Friday, September 10, 2010 5:55 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 10:19 AM
Points: 934, Visits: 1,750
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
Post #983677
Posted Friday, September 10, 2010 6:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:59 AM
Points: 5,930, Visits: 8,181
mukeshkane (9/10/2010)
-- 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

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



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #983682
Posted Friday, September 10, 2010 6:12 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 1:19 AM
Points: 143, Visits: 45
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??
Post #983688
Posted Friday, September 10, 2010 6:27 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:59 AM
Points: 5,930, Visits: 8,181
mukeshkane (9/10/2010)
Is I am still missing something??

Yes.

The line

ALTER TABLE MyTable DROP CONSTRAINT PK_MyIdGO

should read

ALTER TABLE MyTable DROP CONSTRAINT PK_MyId

(That is, remove the GO at the end).

When I wrote that you were missing a GO, I had not seen that the GO was there, but in the wrong place. You added the GO, but kept the extraneous one at the end of the line. Those two extra letters invalidated the DROP CONSTRAINT (because there is no constraint named PK_MyIDGO; the proper constraint name is PK_MyID).



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #983698
Posted Friday, September 10, 2010 6:31 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 1:19 AM
Points: 143, Visits: 45
Thanks finally u got me...
Post #983702
Posted Friday, September 10, 2010 7:27 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, July 25, 2014 9:27 AM
Points: 649, Visits: 470
Great Question!!


Post #983732
Posted Friday, September 10, 2010 7:52 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:56 AM
Points: 3,924, Visits: 1,607
Good question. I had not disabled index as of now or never tried. Thought that it would fetch data but would take time, period.

But, never knew that disabling index would throw error and would not return any thing.

Msg 8655, Level 16, State 1, Line 5
The query processor is unable to produce a plan because the index 'PK_MyId' on table or view 'MyTable' is disabled.


SQL DBA.
Post #983760
Posted Friday, September 10, 2010 7:54 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 7:45 AM
Points: 3,879, Visits: 3,622
vk-kirov (9/10/2010)
The question would be more educational if it had no 'DROP CONSTRAINT' statement. In this case I would probably have answered wrong


Yes, I agree.
Post #983761
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse