9

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?
3

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

6

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

  • Yes, SQL Server supports object level renames using the system stored procedure sp_rename. – gonsalu Oct 28 '11 at 13:03
  • I was able to alter a column via this way with 3.5M rows within 1 minute. – Mohsen Afshin Jun 12 '17 at 4:21
2

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.

1

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

  • In my case, my column is simply full of NULLs, so I'm thinking this would be the way to go, creating a new completely NULL column with the right name should take no time at all... then deleting the old column, instead of dealing with millions of inserts... – Zack Apr 7 '17 at 19:58
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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