Index Architecture and Behavior - SQL Server Query Performance Tuning - page 120

To understand how the selectivity of an index key column affects the use of the index, take a look at the
MaritalStatus column in the HumanResources.Employee table. If you run the previous query, you'll see that it
contains only two distinct values in 290 rows, which is a selectivity of .0069 and a density of .5. A query to look only for
a MaritalStatus of M would look like this:
SELECT e.*
FROM HumanResources.Employee AS e
WHERE e.MaritalStatus = 'M'
AND e.BirthDate = '1984-12-05'
AND e.Gender = 'M';
This results in the execution plan in Figure 8-10 and the following I/O and elapsed time:
Table 'Employee'. Scan count 1, logical reads 9
CPU time = 0 ms, elapsed time = 49 ms.
Figure 8-10. Execution plan with no index
The data is returned by scanning the clustered index (where the data is stored) to find the appropriate values
where MaritalStatus = 'M' . (The other operators will be covered in Chapters 14 and 15.) If you were to place an
index on the column, like so, and run the query again, the execution plan remains the same.
CREATE INDEX IX_Employee_Test ON HumanResources.Employee (Gender);
The data is just not selective enough for the index to be used, let alone be useful. If instead you use a composite
index that looks like this:
CREATE INDEX IX_Employee_Test ON
HumanResources.Employee (BirthDate, Gender, MaritalStatus)
WITH (DROP_EXISTING = ON) ;
and then rerun the query to see the execution plan in Figure 8-11 and the performance results, you get this:
Table 'Employee'. Scan count 1, logical reads 4
CPU time = 0 ms, elapsed time = 38 ms.
Figure 8-11. Execution plan with a composite index