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

Question

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:

1. Why does it take so long to apply the alter?
2. Is there a better way to do it?

Answer 1

1) One would need more info on the complete structure of the table + non clustered indexes to correctly figure out what's happening but my suspicion is something to do with the NULL bitmap.

Please refer to for more details on the topic. http://www.sqlskills.com/BLOGS/PAUL/post/Misconceptions-around-null-bitmap-size.aspx

http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(630)-three-null-bitmap-myths.aspx

2) Yes, provided you have the storage space, create a new table with correct nullability and transfer the data in multiple batches to avoid excessive log growth and switch the table using the technique listed below. I have done this several times with little to no downtime at all.

http://jahaines.blogspot.com/2009/12/sql-server-2005-how-to-move-10-millions.html

Answer 2

Would it be faster to:

1. Create a new table with the correct definition for Column1
2. INSERT INTO <NewTable> SELECT * FROM <OriginalTable>;
3. Rename OriginalTable to OriginalTable_old; Rename NewTable to OriginalTable
4. 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.)

Answer 3

Every row needs to be touched when changing the column from NOT NULL to NULL, which is why it takes so long to complete. There isn't any way to make it take less time.

Answer 4

Another option is to create a new column with correct definition to the table in question, Update the column with data from old column, then drop the old column.

Or You can refer to an older post from SE that treats a similar issue on a INT column.

https://stackoverflow.com/questions/4311559/sql-server-performance-for-alter-table-alter-column-change-data-type

Answer 5

If you open up the SQL activity monitor while the command is running you will be able to tell if it is actually processing it or whether it is waiting for some form of exclusive lock of the table.