Do indexes work with "IN" clause

Question

If I have a query like:

Select EmployeeId  From Employee  Where EmployeeTypeId IN (1,2,3) 

and I have an index on the EmployeeTypeId field, does SQL server still use that index?

Answer 1

Yeah, that's right. If your Employee table has 10,000 records, and only 5 records have EmployeeTypeId in (1,2,3), then it will most likely use the index to fetch the records. However, if it finds that 9,000 records have the EmployeeTypeId in (1,2,3), then it would most likely just do a table scan to get the corresponding EmployeeIds, as it's faster just to run through the whole table than to go to each branch of the index tree and look at the records individually.

SQL Server does a lot of stuff to try and optimize how the queries run. However, sometimes it doesn't get the right answer. If you know that SQL Server isn't using the index, by looking at the execution plan in query analyzer, you can tell the query engine to use a specific index with the following change to your query.

SELECT EmployeeId FROM Employee WITH (Index(Index_EmployeeTypeId )) WHERE EmployeeTypeId IN (1,2,3) 

Assuming the index you have on the EmployeeTypeId field is named Index_EmployeeTypeId.