Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?
share|improve this question
add comment

5 Answers

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.

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

share|improve this answer
add comment

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.)

share|improve this answer
    
Yes, SQL Server supports object level renames using the system stored procedure sp_rename. –  Gonsalu Oct 28 '11 at 13:03
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.