Changing a Column from NOT NULL to NULL in T-SQL

I'm using Enterprise Manager to alter the structure of a table that has 3 million rows. Enterprise Manager locks up whenever I try to change the nullability of one column from NOT NULL to NULL. Is this problem a known bug?

The problem might be recognized as a bug if Enterprise Manager were actually locking up. However, I suspect a different problem is causing your glitch. In T-SQL, you can use the ALTER keyword to change the nullability of a column. This operation is relatively efficient, even for large tables. However, Enterprise Manager doesn't use ALTER to change nullability.

If you use SQL Server Profiler to trace the activity of Enterprise Manager, you'll see that Enterprise Manager can issue more than 150 commands that change the nullability of a column. (I tested this fact by changing the nullability of the phone column in Pubs..authors.) But Enterprise Manager doesn't use the ALTER keyword. Instead, Enterprise Manager creates a new table that has the new schema with the changed nullability setting, uses an INSERT INTO statement to move the data into the new table, then deletes the old table. Using INSERT INTO to populate a 3 million—row table could take a long time depending on your server's capacity. This slow processing time could give the appearance that Enterprise Manager has locked up. To avoid the problem, use the ALTER command directly when updating the schema of large tables.

Learn more: Inside ALTER TABLE 

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.