ALTER COLUMN in SQL Server Management Studio…why am I hanging?

Another good tip for beginners -  remember that if you make a simple change in SQL Server Management Studio (SSMS) by opening up the table in design mode, you might see your entire IDE hang; the reason is because SQL Server could be doing a complete DML operation in the background, moving all of your rows into another table. Notice that I take a very large table – many rows – and alter a column to make it “NULLable”:

In profiler you can see that SQL Server escalated locking, created a new table, and the inserted rows from the existing table using HOLDLOCK and TABLOCKX hints. You’ll probably never be able to select out of, in this case, Tmp_tblCallLog, by the way. SSMS has chosen to move the data out into a new table, which may take a considerable time, my table here with about 50 million rows.

I know that there’s a better way of doing this, so I kill the process…SQL Server will roll back whatever it tried to do of course, so be prepared for this if you happen to go down this path:

The best way to change this column here is to simply do it in TSQL; you won’t get the huge background operation by SQL Server…profiler shows a simple change to the entire column without moving all of the data around, renaming temp tables, etc.

Thanks,

Lee

------------------------