Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Miscellaneous
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