Alter table modify column on a huge table containing more than 25 million records

Question

I want to modify a column on a huge table containing more than 25 million records. I am using sybase 12.5.4. I have read through these posts

What is the most efficient way to alter a column definition in a table with millions of rows

and

ALTER TABLE on a large table with an indexed column

My current strategy is

• Drop all non clustered index
• Drop clustered index
• Alter table modifcy column varchar (32) -- column is char(20) presently
• Recreate clustered index
• recreate non clustered index

Does it make sense. Is there a faster method do it ?

Edit :

• Table Data Size : 16.7 GB
• Total rows : 25835155
• avg row size : .64 KB
• 8 Non Clustered Index
• Yes,column I am changing is the second column in a 2 column NC index

Answer 1

You should only need to drop and recreate the affected NC index.

Saying that, on a test server, you can see the differences in doing this compared to your strategy of dropping all indexes above.

I reckon dropping/creating the single index would be quicker overall because the data will be shifted around twice otherwise: once char to varchar, another to build the clustered index. Then you have the NC creation overhead.