When to create a SQL Server non-clustered index? How do non-clustered indexes sit with SQL Serrver clustered indexes?

It’s not the purpose of this post to describe a SQL Server non-clustered index, which you will find in BOL

Firstly, the main differences with between SQL Sever Clustered Index and non-clustered indexes are:

1) The clustered index sorts table data on physical

2) Non-clustered indexes “point” back to the clustered indexes data pages

A checklist for SQL Server non-clustered indexes. It is important that all index definitions are profiled with the nature of the usage and query demands.

1) In a WHERE clause, if exact matches are returned, add a non-clustered index

2) If the result sets are small create a non-clustered index. If the result sets are large more data pages are read – therefore performance may degrade with a non-clustered index.

3) Look for distinct value groupings that are not found in a clustered index, such as name and date of birth

4) Foreign key columns with joins not in the clustered index

These are general guidelines, test all implementations of indexes against real data sets and queries. Don’t forget to maintain SQL Server statistics

Republished from http://www.sqlserver-dba.com.