Sql-server – the most efficient way to alter a column definition in a table with millions of rows

database-designsql-server-2008

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?

Best Answer

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

Related Solutions

Sql-server – T SQL Table Valued Function to Split a Column on commas

Re-worked it slightly...

DECLARE @FoundIndex INT
DECLARE @ReturnValue VARCHAR(MAX)

SET @FoundIndex = CHARINDEX(@delimiter, @String)

WHILE (@FoundIndex <> 0)
BEGIN
      SET @ReturnValue = SUBSTRING(@String, 0, @FoundIndex)
      INSERT @SplitValues (Asset_ID) VALUES (@ReturnValue)
      SET @String = SUBSTRING(@String, @FoundIndex + 1, len(@String) - @FoundIndex)
      SET @FoundIndex = CHARINDEX(@delimiter, @String)
END

INSERT @SplitValues (Asset_ID) VALUES (@String)

RETURN

Mysql – Efficient way to move rows across the tables

I think its faster (feel free to test) to keep the record in the same table while processing, but be sure to set an index on the column(s) that track which processor is using them.

Also use MyISAM tables if you don't require InnoDB's features. The ARCHIVE table can best be set to the archive type, so it does not make space for updates (if you don't need to write to it)

Best Answer

Related Solutions

Sql-server – T SQL Table Valued Function to Split a Column on commas

Mysql – Efficient way to move rows across the tables

Related Question