Disabling Indexes

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

  • Thanks finally u got me...

  • Great Question!!

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

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

  • Oops. I figured it out.

  • The explaination was very interesting (which I learned from) but I felt the question didn't really illistrate the point very well, i.e. the net result was drop an index and run a select on the table. Who would have thought select * from table returns all rows.

  • Thanks for the question

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Hello!

    Thank-you all for your kind comments and feedback.

    I am sorry that a few of you did have some trouble trying to get the code running on your servers. I also apologize for not having the time during the day to reply to all who took the time out to comment (had a busy day at home and at office today).

    What fascinated me the most about this was that it taught me a little something about constraints and how SQL Server uses them (i.e. the use of the constraint made SQL look for the index, which was disabled).

    To all those who read the question and took the time to attempt it - a very big thank-you from me. To all those who learnt something new, SQL is magical and this question is even more motivation to learn more about it!

    Have a wonderful weekend ahead!

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

    Follow me on
    Twitter: @sqltwins

  • SanjayAttray (9/10/2010)


    Good question. I had not disabled index as of now or never tried. Thought that it would fetch data but would take time, period.

    Same here; but I've seen countless disabled constraints, as well as enabled but Untrusted constraints.

  • Thanks for the nice question.

    It would have been tough if you didn't have "-- Drop the associated constraints" in the statements.

    [font="Verdana"]Regards,
    Rals
    [/font].
  • Hello!

    The goal of this question was to have a deeper understanding of how SQL Server behaves, and not making the questions harder. Emphasis is therefore on the fact that if we have the constraints enabled, SQL does try to use the index which it cannot do because it's disabled. If one knows this concept, the answer is "easy" - otherwise it's "difficult"/"harder".

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

    Follow me on
    Twitter: @sqltwins

  • Yes nakul...

    [font="Verdana"]Regards,
    Rals
    [/font].
  • Nakul Vachhrajani (9/13/2010)


    Emphasis is therefore on the fact that if we have the constraints enabled, SQL does try to use the index which it cannot do because it's disabled.

    Actually, the reason you get an error if the index is disabled is that, for a clustered index, the index IS the table. As a result, disabling the clustered index equates to disabling the table.

    If you use a nonclustered rather than a clustered index, then table is still accessible even with the index disabled. You can try this for yourself, by simply changing the "CLUSTERED" to "NONCLUSTERED" in the constraint declaration for the primary key.


    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/

  • Nice question. Thanks...

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 15 posts - 16 through 30 (of 34 total)

You must be logged in to reply to this topic. Login to reply