SQL Indexing and SQL Performance Part 2: Clustered and Non-Clustered - % Lefteris Karafilis

PROS

• Fast to return large range of data
• Fast for presorted results

• Wide keys do not reflect on other indexes
• Frequently updated key columns do not reflect on other indexes
• Can be assigned on different FileGroup
• Many non-clustered indexes per table
• Smaller size than clustered indexes due to column subsets

CONS

• Frequently updated key columns reflect on non-clustered indexes
• Wide keys increase the size of the non-clustered indexes
• Only one clustered index per table

• Generally slower than clustered indexes due to bookmark lookup (except for covering indexes).
• Not recommended for returning large data sets (except for covering indexes).

Read full article from SQL Indexing and SQL Performance Part 2: Clustered and Non-Clustered - % Lefteris Karafilis