Disabling Indexes

  • Comments posted to this topic are about the item Disabling Indexes

    Thanks & Regards,
    Nakul Vachhrajani.
    http://nakulvachhrajani.com

    Follow me on
    Twitter: @sqltwins

  • Nice question, thanks!

  • "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.

  • This was removed by the editor as SPAM

  • The question would be more educational if it had no 'DROP CONSTRAINT' statement. In this case I would probably have answered wrong 🙂

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

  • nice one thanks...

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

  • Fascinating. Thanks

  • still getting the same error message

  • 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/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

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

  • 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/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • 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