Disabling index by using select query

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Disabling index by using select query

Disabling index by using select query

Rate Topic

Display Mode

Topic Options

Author

Message

DBA_1276

Posted Tuesday, September 07, 2010 5:53 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 06, 2011 12:54 AM
Points: 158, Visits: 151

Hi all,

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

Thankq

Post #981481

Grant Fritchey

Posted Tuesday, September 07, 2010 6:27 AM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:41 AM
Points: 13,383, Visits: 25,189

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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #981497

scott.pletcher

Posted Tuesday, September 07, 2010 3:37 PM

SSC Veteran

Group: General Forum Members
Last Login: Friday, December 31, 2010 9:46 AM
Points: 274, Visits: 473

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

Post #981953

Lowell

Posted Tuesday, September 07, 2010 4:18 PM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:33 PM
Points: 11,648, Visits: 27,768

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #981967

« Prev Topic | Next Topic »

Permissions