Advantages and disadvantages of clustered indexes
What are the advantages and disadvantages of clustered indexes?
Software/Hardware used:
Software/Hardware used:
ASKED:
January 6, 2009 4:41 PM
UPDATED:
January 21, 2009 7:36 PM
Answer Wiki:
Clustered index
advantages -
Search results are quicker when records are retrieved by the cluster index
other indexes that use fields that are a part of the cluster index might use less data space
Disadvantage
It takes longer to update records if only when the fields in the clustering index are changed.
Avoid clustering index constructions where there is a risk that many concurrent inserts will happen on almost the same clustering index value
To see all answers submitted to the Answer Wiki: View Answer History.
Searches against a clustered index are not quicker than against a nonclustered index.
Searches against a nonclustered index will appear slower is the clustered index isn’t build correctly, or it does not include all the columns needed to return the data back to the calling application. In the event that the non-clustered index doesn’t contain all the needed data then the SQL Server will go to the clustered index to get the missing data (via a lookup) which will make the query run slower as the lookup is done row by row.