Force a SQL stmt to use an Index

  • If there is a SQL command that forces a SQL SELECT statement to use a specific index, what it is?



    Matthew Mamet

  • Take a look in BOL for hints. Sometimes it's appropriate, but think twice before doing it - what will work in one case may not work as well in another. Your code also becomes dependent on that particular index to work. If you (or another DBA) in the future decides to drop the index, your code will break.

    Andy

  • what's BOL?



    Matthew Mamet

  • Books Online.

  • The exact syntax is:

    SELECT field_names

    FROMtable_name WITH (INDEX = index_name)

    ORDER BY field_names



    Matthew Mamet

  • I concur with Andy. You probably want to avoid this. You are assuming you know more than the program for a particular statement, which may not be true for different load levels. Also, this creates a point of maintenance. If you create a new index or remove this one (for some reason), will you remember to update this query?

    Steve Jones

    steve@dkranch.net

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply