SQL Server Performance Forum – Threads Archive
Updates on Clustered IndexesWhat are the pros and cons of putting a clustered index on a column that has a lot of updates on it? It seems that there would be a lot of overhead in reorganization of the table to accommodate the new index values, and any non-clustered indexes would have to be updated. Anybody have anything to say?
It is better to have your clustered index on a column that is static,unique,short and ever growing.
Roji. P. Thomas
The optimizer reliably chooses the most effective index in the majority of cases. The overall index design strategy should be to provide a good selection of indexes to the optimizer, and trust it to make the right decision. This reduces analysis time and gives good performance over a wide variety of situations.
-Examine the WHERE clause of your SQL queries, because this is the primary focus of the optimizer.
-Use narrow indexes, the optimizer can rapidly and effectively analyze hundreds, or even thousands, of index and join possibilities. Having a greater number of narrow indexes provides the optimizer with more possibilities to choose from, which usually helps performance. Even UPDATE and DELETE operations are often accelerated by clustered indexes, because these operations require much reading. A single clustered index per table is allowed, so use this index wisely. Queries that return numerous rows or queries involving a range of values, are good candidates for acceleration by a clustered index.
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
This posting is provided AS IS with no rights for the sake of knowledge sharing.