Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Disabling index by using select query Expand / Collapse
Author
Message
Posted Tuesday, September 7, 2010 5:53 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 6, 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
Posted Tuesday, September 7, 2010 6:27 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:27 PM
Points: 13,776, Visits: 28,178
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 Query Performance Tuning
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
Posted Tuesday, September 7, 2010 3:37 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC 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
Posted Tuesday, September 7, 2010 4:18 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:39 PM
Points: 12,903, Visits: 32,143
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 »

Add to briefcase

Permissions Expand / Collapse