October 9, 2008 at 1:44 pm
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.
October 9, 2008 at 2:28 pm
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 9, 2008 at 2:37 pm
After running a few execution plans on several variations of code, I can see that you are absolutely correct. Thanks for your promptness.
October 10, 2008 at 11:04 pm
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply