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

I need to change a column from NOT NULL to NULL in a table that contains millions of rows. I've tried a simple

alter table Table1 ALTER COLUMN Column1 XML NULL

but it takes forever. So here are my questions:

• Why does it take so long to apply the alter?

• Is there a better way to do it?

Would it be faster to:

• Create a new table with the correct definition for Column1

• INSERT INTO SELECT * FROM ;

• Rename OriginalTable to OriginalTable_old; Rename NewTable to OriginalTable

• Validate and Drop OriginalTable_old

The advantage here is that you do not hold a lock on the Original table for the duration of the operation. The table should only be locked during the rename phase. (It assumes that SQL Server supports an object level rename.)

StackExchange Database Administrators Q#2031, answer score: 6