1010
Part VII: Performance Tuning and Optimization
Since SQL Server 2005, additional unsorted columns can be included in the leaf level. The
employee’s title and department columns could be added to the previous index, which is
extremely useful in designing covering indexes.A SQL Server table may have up to 999 nonclustered indexes, but I’ve never seen a well-
normalized table that required more than a dozen well-designed indexes.Composite Indexes
A composite index is a clustered or nonclustered index that is defi ned on multiple col-
umns. Consider the clustered index telephone book example; the keys of the composite
index were based on the last name and fi rst name of the individual, in that order. The
ordering of the columns in a composite index is important. For a search to take advan-
tage of a composite index, it must include the index columns from left to right. If the
composite index is lastname, firstname, a search for only the firstname cannot
seek the fi rst name because it must fi rst know the last name. The fi rst name is not
found independently in sorted order but is instead based on the lastname column. If
you need to perform a search for lastname, or lastname and firstname, you can
effi ciently use the index.Various methods of indexing for multiple columns are examined in the section “The Path of the Query”,
later in this chapter.Unique Indexes and Constraints
A Primary key and unique constraints are the method you use to uniquely identify a row.
Indexes and primary keys are intertwined and a primary key must always be indexed. By
default, creating a primary key automatically creates a unique clustered index, but it can
optionally create a unique nonclustered index instead.A unique index, as its name suggests, limits data to being unique. In other words, a
unique index is constraining the data it indexes. A unique constraint builds a unique
index to quickly check the data. A unique constraint and a unique index are the same
thing — creating either one builds a unique constraint/index. The only difference between
a unique constraint/index and a primary key is that a primary key cannot allow nulls, and
a unique constraint/index can permit a single null value.The Page Split Problem
Every index must maintain the key column data in the correct sort order. Inserts, updates,
and deletes affect that data. As the data is inserted or modifi ed, if the index page to which
a value needs to be added is full, SQL Server must split the page into two less full pages
so that it can insert the value in the correct position. Again using the telephone bookc45.indd 1010c45.indd 1010 7/31/2012 10:16:38 AM7/31/2012 10:16:38 AM
http://www.it-ebooks.info