Referencing indexes in a SQL statement

  • 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.

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • After running a few execution plans on several variations of code, I can see that you are absolutely correct. Thanks for your promptness.

  • 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