| Author |
 Topic  |
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2865 Posts |
 Posted - 05/03/2012 : 12:11:50
|
I have a table of about 250 million records, 144 columns and takes up about 135 GB. Our vendor says that that table really needs more columns and some existing columns to be changed from nvarchar x to nvarchar x+ y. This will be rather a painful process obviously. Should I drop the indexes on the table first? The columns that participate in the index will need to have their indexes dropped, but what about dropping the clustered index as well? Is there any advantage to doing that?
Jim
Everyday I learn something that somebody else already knew |
|
|
robvolk
Most Valuable Yak
USA
15557 Posts |
Posted - 05/03/2012 : 12:17:16
|
| Nope, don't drop the clustered. Only drop the indexes that include the columns being changed. The ALTER itself should be quick since you're increasing the column size. Rebuild the non-clustered indexes afterwards, use ONLINE if you have Enterprise Edition. |
 |
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2865 Posts |
Posted - 05/03/2012 : 12:45:07
|
Hi Rob!
Thanks,
I thought that these alter statements would fragment the clustered index bad enough to make them useless. How about going the other way, taking something from nvarchar x+y to nvarchar x, or even nvarchar(30) to int?
Jim
Everyday I learn something that somebody else already knew |
|
|
|
robvolk
Most Valuable Yak
USA
15557 Posts |
Posted - 05/03/2012 : 12:53:50
|
Adding non-nullable columns, changing data types, or making a column smaller would force writes to the data pages, which could fragment the clustered index, but would not affect any non-clustered indexes unless you were also changing the clustering key (and you'd have to drop the clustered index to do that). The query optimizer doesn't consider index fragmentation anyway, it chooses an index based on its statistics.
Edit: Michael J. Swart just finished a really good series on migrating/rebuilding tables while keeping them online:
http://michaeljswart.com/2012/04/modifying-tables-online-part-1-migration-strategy/
(and I'm not just saying that because of my involvement in Part 5) 
I highly recommend a technique like that instead of altering a very large table, unless you can test it and show that the alter will have less impact. |
|
|
| |
Topic |
|
Drop clustered Index?
