INDEXING CONSIDERATIONS - A Developer's Guide to Data Modeling for SQL Server - page 228

In contrast to a clustered index, the B-tree in Figure 10.5 doesn't in-
clude the actual data rows. These pages are filled with pointers to the lo-
cation of the actual rows. Again, for a heap, that is a simple identifier that
points to the page number and rows identifier; for a clustered index, the
pointer goes to the clustered index key for values that fall in the range of
the query.
Why would you want to use a nonclustered index? For the Customers
table, you might want a nonclustered index on the EmailAddress field. The
data is already ordered by LastName, FirstName, so if you have query that
is looking only for the e-mail address, that clustered index may not help
(because e-mail addresses don't have to look like a person's name). So we
could add a nonclustered index to help those queries look for e-mail ad-
dresses by searching ranges of values instead of looking through the entire
table. It works by helping break the e-mail address values into specific
ranges so that we can scan a smaller number of values to find the match-
ing rows. It also assists in queries to retrieve the last name, first name, and
e-mail address of the customer. In more-complex systems, you might see a
clustered index accompanied by several nonclustered indexes that help sat-
isfy various types of queries.
Often, indexes are created to handle specific queries; these are called
covering indexes, because they cover all the fields being referenced by
that query. In the next section we talk more about the considerations for
using indexes.
Other Index Types
Now that you have a basic understanding of indexes and what they are for,
let's take a brief look at some other types of indexes you can use in SQL
Server 2008.
Unique
Unique indexes are indexes that specify that the index key be a unique
value (or set of values) in the table. Both clustered and nonclustered in-
dexes can be unique; primary keys are always a type of unique index.
Indexes with Included Columns
Starting in SQL Server 2005, designers can specify a new clause when cre-
ating their indexes. INCLUDE allows you to specify additional columns in
a nonclustered index. This action adds the columns only to the final level
of the index; there is no reference to the column in the intermediate or