Hi Derek. Thanks for the reply.

You are absolutely correct: you would need sufficient disk space for the operation. The specific calculations don't matter as you can tell by the current size of the table and what you are trying to do with it. So a 300 GB table would need another 300 GB at least if you are keeping most of the columns. The main issue is what do you sacrifice due to requirements. Our requirements are that there is nothing more than a "blip" in the system as most of the time we do not turn the application off. So doing these changes in the current table are (or were) not always possible. Changes in Clustered Index, PK, FKs, adding NOT NULL fields all complicate doing it in place. Yes, you should be able to drop and recreate the Clustered Index using ONLINE = ON (IF you have Enterprise Edition!), and now in SQL Server 2012 you can add a NOT NULL field with a default without locking the table, but I have not tested either of these approaches so know how they work in the real world rather than theory. And again, the extent of the changes might dictate this approach as a requirement and in that case the business needs to accept the sacrifice of having that disk space if their primary concern is no down-time. Or maybe you have an update trigger on this table and/or Change Tracking and/or Change Data Capture that would be adversely affected by updating the current table. However, if you just want to change a column or two then yes, you should first look to doing that in-place by just renaming the column and dropping the old one (or whatever the situation demands).

Take care,

Solomon...