Index Creation Order
cardsharp
Posts: 5
SQL Compare Version: 5.3.0.44
I've noticed that SQL Compare generates scripts that create indexes in a sub-optimal order. In the scripts it first creates unclustered indexes, and finishes up with the clustered index.
Creating (or rebuilding) a clustered index will cause all the non-clustered indexes on the table to be rebuilt.
It is much faster to create the clustered index first, then create non-clustered indexes because SQL Server won't have to rebuild the other indexes. In my testing on large tables (over 100 million rows) creating the clustered index first, followed by non-clustered indexes is twice as fast.
You may want to consider this for a future release. I know it would help me out!
I've noticed that SQL Compare generates scripts that create indexes in a sub-optimal order. In the scripts it first creates unclustered indexes, and finishes up with the clustered index.
Creating (or rebuilding) a clustered index will cause all the non-clustered indexes on the table to be rebuilt.
It is much faster to create the clustered index first, then create non-clustered indexes because SQL Server won't have to rebuild the other indexes. In my testing on large tables (over 100 million rows) creating the clustered index first, followed by non-clustered indexes is twice as fast.
You may want to consider this for a future release. I know it would help me out!
Comments
Thanks for your feedback, the developer concerned is out of the office for a while. I will mention this to him when he gets back and find out if there was a reason why we script the indexes this way around, and if it is possible to implement your request I will raise a feature request for a future version.
Regards,
Jonathan
-Project Manager
-Red Gate Software Ltd