Database Reference
In-Depth Information
Additional Characteristics of Indexes
Other index properties can affect performance, positively and negatively. A few of these behaviors are explored here.
Different Column Sort Order
SQL Server supports creating a composite index with a different sort order for the different columns of the index.
Suppose you want an index with the first column sorted in ascending order and the second column sorted in
descending order to eliminate a sort operation, which can be quite costly. You could achieve this as follows:
CREATE NONCLUSTERED INDEX i1 ON t1(c1 ASC, c2 DESC);
Index on Computed Columns
You can create an index on a computed column, as long as the expression defined for the computed column meets
certain restrictions, such as that it references columns only from the table containing the computed column and it is
deterministic.
Index on BIT Data Type Columns
SQL Server allows you to create an index on columns with the
BIT
data type. The ability to create an index on a
BIT
data type column by itself is not a big advantage since such a column can have only two unique values, except for the
rare circumstance where the vast majority of the data is one value and only a few rows are the other. As mentioned
previously, columns with such low selectivity (number of unique values) are not usually good candidates for indexing.
However, this feature comes into its own when you consider covering indexes. Because covering indexes require
including all the columns in the index, the ability to add the
BIT
data type column to an index key allows covering
indexes to have such a column, if required (outside of the columns that would be part of the
INCLUDE
operator).
CREATE INDEX Statement Processed As a Query
The
CREATE INDEX
operation is integrated into the query processor. The optimizer can use existing indexes to reduce
scan cost and sort while creating an index.
Take, for example, the
Person.Address
table. A nonclustered index exists on a number of columns:
AddressLine1
,
AddressLine2
,
City
,
StateProvinceld
, and
PostalCode
. If you needed to run queries against the
City
column with the existing index, you'll get a scan of that index. Now create a new index like this:
CREATE INDEX IX_Test
ON Person.Address(City);
You can see in Figure
9-17
that, instead of scanning the table, the optimizer chose to scan the index in order
to create the new index because the column needed for the new index was contained within the other
nonclustered index.
Figure 9-17.
Execution plan for CREATE INDEX
