Post reply

Disabling index by using select query

  • September 7, 2010 at 5:53 am

    #225698

    Hi all,

    Is there any way to disable an index by using select query.

    Thankq

  • September 7, 2010 at 6:27 am

    #1217265

    You can use ALTER INDEX for a nonclustered index to disable it. If you do this for a clustered index, it makes the data inaccessible.

    ALTER INDEX myIndexName ON myTable DISABLE

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • September 7, 2010 at 3:37 pm

    #1217650

    There's not any hint to disable / ignore an index just for a given SELECT statement (you could force it to use a different index, but even that might not prevent use of the other index also).

    Scott Pletcher, SQL Server MVP 2008-2010

  • September 7, 2010 at 4:18 pm

    #1217664

    if the query has a WHERE statement, you could make the arguments non-SARG-able, and force it to use a table scan, but it would still use an index(probably the clustered index) to gather the data;

    if you want to avoid a specific index you can use a hint to tell it a specific index to use instead.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 4 posts - 1 through 3 (of 3 total)

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