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

Referencing indexes in a SQL statement Expand / Collapse
Author
Message
Posted Thursday, October 9, 2008 1:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 25, 2013 8:12 AM
Points: 5, Visits: 123
Is there an advantage to reference which index you want to use first with your query? I was under the impression that it used the clustered index first regardless but someone recently told me that you can reference it and it would use that index first. This is in SQL Server 2005. An example is below.

select *
from table1 with(index (NameOfIndex))

Thanks in advance.
Post #583583
Posted Thursday, October 9, 2008 2:28 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:02 AM
Points: 42,470, Visits: 35,543
There's a major disadvantage to doing that. If you use a query hint you are forcing the optimiser to pick a plan using that index, even if another index would be much more optimal for the query.

Unless you are really, 100% sure that you know better than the query optimiser what index is most optimal for the query, don't use an index hint.



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 #583595
Posted Thursday, October 9, 2008 2:37 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 25, 2013 8:12 AM
Points: 5, Visits: 123
After running a few execution plans on several variations of code, I can see that you are absolutely correct. Thanks for your promptness.
Post #583596
Posted Friday, October 10, 2008 11:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 28, 2014 9:50 AM
Points: 8, Visits: 105
Ditto. Further, using an index optimizer hint can be tricky to maintain. If the index is removed sometime in the future, you will need to "track" through your code and remove the reference. It's much safer to let the server decide and keep your statistics updated.
Post #584341
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse