Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
 Posted - 2012-05-03 : 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?JimEveryday I learn something that somebody else already knew |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-05-03 : 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
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-05-03 : 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?JimEveryday I learn something that somebody else already knew |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-05-03 : 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. |
|
|
|
|
|
|
|
|
Drop clustered Index?