From: zdtips@zdtips.com

Sent: Tuesday, February 15, 2000 4:11 PM
To: sqltips@zdtips.com
Subject: SQL Server Tips, 02/16/2000

Choosing between clustered and nonclustered indexes in SQL Server

Because SQL Server stores the pages of a table in order by its
clustered index, you'll find you get the best performance when you
create your clustered index based on queries for which you'll want
to retrieve a range of rows instead of a single row. For example,
consider a customer table in which you've defined account number and
addressing information for customers from all over the world. You
might think your best choice for a clustered index is the customer
account number, but a better choice would be to create the clustered
index on a value such as the customer's ZIP or postal code. (You
might use such an index if you frequently query the customer table
to retrieve rows based on ZIP code for bulk-mailing purposes.)
You'll find that a clustered index will optimize the retrieval of
these rows more than a nonclustered index because the data is
already in order by the ZIP code. Thus, SQL Server needs to navigate
your clustered index only once to locate the appropriate pages--and
then display the rows.