Database Reference
In-Depth Information
Figure 8-9.
Number of pages for a wide, nonclustered index
A large index key size increases the number of index pages, thereby increasing the amount of memory and disk
activities required for the index. It is always recommended that the index key size be as narrow as you can make it.
Drop the test table before continuing.
DROP TABLE dbo.Test1;
Examine Column Uniqueness
Creating an index on columns with a very low range of possible unique values (such as
MaritalStatus
) will not
benefit performance because the query optimizer will not be able to use the index to effectively narrow down the rows
to be returned. Consider a
MaritalStatus
column with only two unique values:
M
and
S
. When you execute a query
with the
MaritalStatus
column in the
WHERE
clause, you end up with a large number of rows from the table (assuming
the distribution of
M
and
S
is relatively even), resulting in a costly table or clustered index scan. It is always preferable
to have columns in the
WHERE
clause with lots of unique rows (or
high selectivity
) to limit the number of rows accessed.
You should create an index on those columns to help the optimizer access a small result set.
Furthermore, while creating an index on multiple columns, which is also referred to as a
composite index
, column
order matters. In many cases, using the most selective column first will help filter the index rows more efficiently.
■
the importance of column order in a composite index is explained later in the chapter in the “Consider Column
order” section.
Note
From this, you can see that it is important to know the selectivity of a column before creating an index on it.
You can find this by executing a query like this one; just substitute the table and column name:
SELECT COUNT(DISTINCT e.MaritalStatus) AS DistinctColValues,
COUNT(e.MaritalStatus) AS NumberOfRows,
(CAST(COUNT(DISTINCT e.MaritalStatus) AS DECIMAL)
/ CAST(COUNT(e.MaritalStatus) AS DECIMAL)) AS Selectivity,
(1.0/(COUNT(DISTINCT e.MaritalStatus))) AS Density
FROM HumanResources.Employee AS e;
The column with the highest number of unique values (or selectivity) can be the best candidate for indexing
when referred to in a
WHERE
clause or a join criterion. You may also have the exceptional data where you have
hundreds of rows of common data with only a few that are unique. The few will also benefit from an index. You can
make this even more beneficial by using filtered indexes (discussed in more detail later).
