Impact of ALTERING column in enormous table NOT NULL to NULL.

Question

SQL Server 2005: 240 million row table. Column requirements changing from NOT NULL to NULL.

Generally bad practice (and often impossible) to use ALTER statements when dealing with tables this big, however, trying to avoid rebuilding the table, if possible.

Tested ALTER statement against a dev table containing 20m rows, and the statement executed successfully in <1 second.

Question: This appears that it should work against the huge production table... but can anyone out there point out risks or impact based on experience (other than the obvious)?

Many thanks.

Answer 1

Never had a problem doing this myself on large tables.

If you were changing the datatype or shortening the size of a column for example, that would involve more work. But for a change from NOT NULL to NULL, it shouldn't be too taxing a task.

I would however, as a standard approach, schedule the update in during a quiet period.

Answer 2

Shouldn't be a problem going from NOT NULL to NULL. I've done this before and haven't had a problem with it.

The only thing I can come up with that might be an issue is getting the lock for the entire table.

Answer 3

A change from NOT NULL to NULL is fine because existing data need not be checked/changed. The other direction however would need all existing data to be checked; so that might be problematic.

I suggest you watch out for the possibility that existing queries (written when the column was still NOT NULL) will not now be 'broken' as a result of the possibility for NULL values. The catch is that it may be tricky to confirm that the logic is still sound in all cases because you're unlikely to receive any obvious errors.

Pay careful attention to aggregation functions.