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
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
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.
Republished from SQL Server DBA [65 clicks].
Read the original version here [32134 clicks].