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 employeeIDType 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.

• How do composite indexes work?

  I've created composite indexes ( indices for you mathematical folk) on tables before with an assumption of how they worked. I was just curious if my assumption is correct or not. I assume that wh…

• Does OR clause suppress indexes in oracle?

  Does OR clause suppress Indexes, If yes can someone provide appropriate example? create table test2(field1 varchar2(100),field2 varchar2(100), field3 number,field4 varchar2(100)); crea…

• How do I ensure that this query sticks to indexes?

  I have a database with two tables. The one contains accounts, and the other contains over 2 million rows containing addresses and their coordinates. Obviously with such an amount of rows, any time a…

• What are Covering Indexes and Covered Queries in SQL Server?

  Can you explain the concepts of, and relationship between, Covering Indexes and Covered Queries in Microsoft's SQL Server?

• Indexes for inner joins with where clause

  If I had the following query: select some cols from tbl_a INNER JOIN tbl_b ON tbl_a.orderNumber = tbl_b.orderNumber where tlb_b.status = 'XX' Assuming both tables have clustered indexes …

• Do indexes suck in SQL?

  Say I have a table with a large number of rows and one of the columns which I want to index can have one of 20 values. If I were to put an index on the column would it be large? If so, why? If I…

• How do indexes work on views?

  Can someone please explain to me in simple English how an index on a view works? I have a fairly simple understanding of indexes on tables; how would indexing a view work differently from just letti…

• How do function indexes work in SQL?

  Does anyone know how function indexes work in databases?

• How can I query between two columns while still taking advantage of indexes?

  Imagine I have a table contains all the chapters of a book and the start/end page from each chapter. chapter | start_page | end_page -------------------------------------- 1 | 1 | 2…

• MySQL not using indexes with WHERE IN clause?

  I'm trying to optimize some of the database queries in my Rails app and I have several that have got me stumped. They are all using an IN in the WHERE clause and are all doing full table scans even …