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.

Source: Rozanne Whalen
Viewed 10734 times