SQL Server Transact-SQL Query Performance Tuning and Optimization Tips

If you find that SQL Server uses a TABLE SCAN instead of an INDEX SEEK when you use an IN or OR clause as part of your WHERE clause, even when those columns are covered by an index, consider using an index hint to force the Query Optimizer to use the index.

For example:

SELECT * FROM tblTaskProcesses WHERE nextprocess = 1 AND processid IN (8,32,45)

takes about 3 seconds, while:

SELECT * FROM tblTaskProcesses (INDEX = IX_ProcessID) WHERE nextprocess = 1 AND processid IN (8,32,45)

returns in under a second. [7.0, 2000] Updated 6-21-2004 Contributed by David Ames


SQL Server Transact-SQL Query Performance Tuning and Optimization Tips