Home > Articles > Data > SQL Server

  • Print
  • + Share This
Like this article? We recommend


If the table has a small number of rows (10,000 or less), indexes won't make a great deal of difference, so don't even waste your time there. In fact, if SQL Server can scan a table faster than it can scan an index, it will ignore the index.

Occasionally SQL Server makes a wrong decision about an index. Nine times out of ten SQL Server can outsmart you, but if you think you can outsmart the computer, you can use index hints to force using a particular index. The syntax for forcing the use of a particular index is simple:

SELECT column1
FROM table_name WITH (INDEX = N)

N stands for indid value for a particular index in the sysindexes table. All clustered indexes have a indid value of 1. If you need to look up a value for a particular index, use the following query:

SELECT indid FROM sysindexes WHERE name = 'index_name'

For instance, to force using a clustered index on the titleauthors table examined earlier, I could change the T-SQL code as follows:

SELECT au_lname, au_fname, title
FROM authors a INNER JOIN titleauthor b WITH (INDEX = 1)
ON a.au_id = b.au_id
INNER JOIN titles c ON c.title_id = b.title_id
WHERE title LIKE '%computer%' AND state = 'CA'
ORDER BY title
  • + Share This
  • 🔖 Save To Your Account