Disabling index by using select query

Question

Disabling index by using select query

DBA_1276

SSC Eights!

Points: 878
More actions
September 7, 2010 at 5:53 am

#225698

Hi all,
Is there any way to disable an index by using select query.
Thankq

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

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

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 1

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 Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

scott.pletcher SSCertifiable Points: 5958 More actions · Answer 2

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

Lowell SSC Guru Points: 323518 More actions · Answer 3

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!