Advantages and disadvantages of clustered indexes
Home > IT Answers > Database > Advantages and disadvantages of clustered ind...
SQL Server Ask the Experts
2445 pts.
0
Q:
Advantages and disadvantages of clustered indexes
Clustered Index, SQL Server Clustered Index
What are the advantages and disadvantages of clustered indexes?
ASKED: Jan 6 2009  4:41 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
Ctssenthil
30 pts.
0
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • AddThis Social Bookmark Button
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
Last Answered: Jan 21 2009  9:58 AM GMT by Ctssenthil   30 pts.
Latest Contributors: Mrdenny   46810 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

mrdenny   46810 pts.  |   Jan 21 2009  7:36PM GMT

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.

 
0