Performance: ALTER TABLE with default value vs. iterating over all records to set new value For adding values to a new column, I have the following options:


Using default to add value during ALTER TABLE command.
Doing ALTER TABLE without default, then fetching all the records and iterating over all of them to save them with the new value.


I am interested to know how much performance difference should I expect?

I am adding a new column to a table with 14+ million records.

The column needs a default, unfortunately, I'll need to change a lot of code to allow putting default value. However, it's easier to iterate over all the records and save them with the value for the new column.

Before making my decision, I am looking for metrics of performance differences between the two options.

I am working on an application to be used in multiple machines, different environments and DBMS. I wouldn't be installing the application, it's the client's job. The application has scripts to aid them with the same. I understand that without system specifications it's hard to make predictions.

Is making broad predictions possible? As in, accessing all records requires O(n) time, similarly information on time requirements of using default during ALTER TABLE and other processes will also help.
The primary difference you need to be concerned about is whether or not a table lock is acceptable for an extended period of time.  One approach acquires a lock against the table and one acquires locks on rows (if done correctly).

You might also consider, if possible, creating a view against the original table that also includes the new column (and value).  Export the view data using BCP.  Then, drop and recreate the table with the new column and use BCP to import.  You could probably take advantage of being minimally logged.

Many popular databases offer a free version for non-production use. If you want to understand the performance difference your best bet is to run tests on the most common platforms that you expect your clients to use.