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 

Source:Jack Vamvas (http://www.sqlserver-dba.com)

Author: Jack Vamvas (http://www.sqlserver-dba.com)