Contents
How to update rows in a large table?
Dropping and re-creating the clustered index will result in all 83 million rows being written to the log while data is moved in and out of the clustered index. This is a lot of extra I/O that is not necessary for the update to succeed.
What’s the best way to update a table?
Always use a WHERE clause to limit the data that is to be updated 2. If the table has too many indices, it is better to disable them during update and enable it again after update 3. Instead of updating the table in single shot, break it into groups as shown in the above example.
How long does it take to update a table in SQL Server?
As you can see from the above, the query only took around 14 seconds to update 1,000,000 rows. If SOW_Number is more selective, the time required will be proportionally less. As you’ve identified in your question, dropping the clustered index does not help increase performance of the update query.
Can You update 10K rows in a set?
You should not be updating 10k rows in a set unless you are certain that the operation is getting Page Locks (due to multiple rows per page being part of the UPDATE operation). The issue is that Lock Escalation (from either Row or Page to Table locks) occurs at 5000 locks.
Why do I have to update a table every time?
Keeping in mind the columns in the clustered index key are used (duplicated) in every non-clustered index, the space savings from right-sizing the data might be substantial. Rather than update, and most likely have to keep updating this table, why not create computed columns once?
How does dropping the clustered index improve performance?
If SOW_Number is more selective, the time required will be proportionally less. As you’ve identified in your question, dropping the clustered index does not help increase performance of the update query. Understand that all data in a table that has a clustered index is in fact stored in the clustered index.