Database Reference
In-Depth Information
One other thing to keep in mind is the balance between the clustered
index and nonclustered indexes on your table. For most operational data-
bases, each table will have a single clustered index on the primary key
(whether it is a surrogate or a natural key). This means that the data will be
sorted on disk according to its primary usage. Even though you could in-
clude non-key columns in the clustered index, it is usually pointless be-
cause the lowest level of the index is already the data page; the entire row
is actually found when you search the index, regardless of the other values
in the query. Thus, the nonclustered indexes are there to satisfy those
queries that search the data in a different order from its natural order, or
when the clustered key value is not used in the query at all.
In the Customers table from Mountain View Music, we could use a
nonclustered index to satisfy the e-mail lookup. Maintaining this nonclus-
tered index is simple, and it can very easily improve the performance of the
query. We might also consider nonclustered indexes for the phone num-
bers. Additionally, we might consider including the phone number fields in
either the clustered index (not a great idea) or the e-mail nonclustered
index (better). If we create an index with the e-mail, home phone, work
phone, and mobile phone, we are creating a wider index, but we are cre-
ating a single index that SQL Server can use to search for any of those
pieces of data. This is because SQL Server is smart enough to use an index
even if the data it's looking for is in a secondary column of the index. The
index is still pointing to the data, regardless of the order of the columns de-
fined in the index, so it may still be faster than just scanning the table.
What we've created then is a covering index of sorts.
Covering Indexes
By definition, a covering index specifically includes columns from a given
query in order to satisfy that specific query. However, in certain situations,
such as the Customers table we've just discussed, you can create an index
of the columns not included in the clustered index because they might sat-
isfy 85 percent or more of the unknown, or ad hoc, queries. Usually these
indexes are created after a database has been in use for some time and a
DBA has identified a number of varying queries that could use a common
index. However, if you can identify a table that will be the target of these
types of queries (based on your notes), then you might consider creating a
covering index right out of the gate. Fortunately, indexes can be modified,
created, and destroyed after the initial design phase, so this may be a trial-
and-error process.
Search WWH ::
Custom Search