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

Exclude Specific Index From Query Expand / Collapse
Author
Message
Posted Saturday, April 27, 2013 4:36 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 12:56 PM
Points: 4, Visits: 289
This is driving me crazy! I want to exclude a specific index from a query but I can not remember how it's done, this may be an undocumented option as I can't seem to find any information about this feature. Does anyone know how to do this?

Thanks!
Post #1447296
Posted Sunday, April 28, 2013 4:19 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 6:19 AM
Points: 2,963, Visits: 2,982
Hi

You can disable an index. Once you disable it, then you have to rebuild it in order to enable it.
If your index is clustered, then after disabling it, your data will be read only and you must rebuild it for update operations.

Regards
IgorMi




Igor Micev,
SQL Server developer at Seavus
www.seavus.com
Post #1447317
Posted Sunday, April 28, 2013 6:58 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:27 AM
Points: 39,866, Visits: 36,209
Could you be a bit more specific about what you're trying to do and why?

If you mean query execution, there's hints to force specific indexes but no hint to ignore certain indexes. Besides, in most cases the optimiser should be left to chose indexes based on its costing of the queries.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1447322
Posted Sunday, April 28, 2013 6:59 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:27 AM
Points: 39,866, Visits: 36,209
IgorMi (4/28/2013)
If your index is clustered, then after disabling it, your data will be read only and you must rebuild it for update operations.


Disabling a clustered index prevents all access to the table, read or update, until the index is rebuilt





Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1447323
Posted Sunday, April 28, 2013 10:35 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 6:19 AM
Points: 2,963, Visits: 2,982
GilaMonster (4/28/2013)

Disabling a clustered index prevents all access to the table, read or update, until the index is rebuilt



Correct. Just tried for clustered indexes. And, once you disable a clustered index, all nonclustered are also disabled because they use the clustered one.

Thank You
Regards
IgorMi




Igor Micev,
SQL Server developer at Seavus
www.seavus.com
Post #1447332
Posted Sunday, April 28, 2013 11:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 12:56 PM
Points: 4, Visits: 289
Thank you all so much for your replies. I wanted to remove a specific index from consideration when SQL Server is compiling it's query plan, kind of like an Option Index <>. This was a purely academic pursuit as I was certain we had done this in the past however, you are of course correct and this is not possible.

Thanks!
Post #1447338
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse