Alter column to Varchar(max) takes to long

Hi,

I need to modify existing table in my database to varchar(max) from varchar(2000)

This table contains 30 million plus rows and has more than 70 columns.

now when i am running alter command for this it take too long(more than 9 mins) which is not acceptable. . Is their any way to reduce this execution time

Following is the query i am using for this

ALTER TABLE Receipt
ALTER COLUMN CUSTOM VARCHAR(MAX) NULL

Please let me know if you have any suggestion to improve this

TAI
Prashant

Try to add a new column with the new type and then try to do something like:

UPADTE Table
SET
NewCol = Col1,
Col1 = NULL

After that drop the old column. I don′t know if that will save you the additional space the second column will need, but it should be worth a try doing this in one step. If it does not work for you, create a column first copy the data over to the new column, then drop the old one and rename the new one. You will have to do that in a maintaince window to not procude dirty write in the new column.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de