Adding column with default value to large table
performance, sql, sql-server
Solution
Thanks Aaron for your detailed approach, but I did a quick test and the simple approach would be to do the following:
Some background. I'm adding a CompanyID to an existing large table. The ID refers to the company the record belongs to. Default value would be 0. But since this is going into an existing customers prod database, their company ID is 1. We have a generic upgrade script for all our clients, turns out a slight modification to this script for this specific customer yielding significant performance improvements.
INSTEAD OF:
ALTER TABLE myTable ADD CompanyID int NOT NULL CONSTRAINT DF_Constraint DEFAULT 0 (takes about 1min to complete)
UPDATE myTable SET CompanyID = 1 (will take over an hour)
I JUST DO THIS:
ALTER TABLE myTable ADD CompanyID int NOT NULL CONSTRAINT DF_Constraint DEFAULT 1 (takes about 1min to complete)
Then just set the default value back to 0. Now the table will have CompanyID = 1 for all records. BOOM!
Problem
I have a table with 40mil records. I need to add a new INT NOT NULL column to that table, with default value = 0 When adding this column using the following: ``` ALTER TABLE myTable ADD NewColumnID int NOT NULL CONSTRAINT DF_Constraint DEFAULT 0 ``` It sets the NewColumnID to 0 for all records. When running this query on our prod table which has 40mil records, will this take a long time? Because I know doing the following takes a VERY LONG TIME: ``` UPDATE myTable SET NewColumnID = 0 ``` UPDATE: 05 Jan 2020: It's been a while since I've last logged into my stack-overflow account. I noticed this particular question which I posted back in 2013. I've received some bad rep for this question and I can now see why. I had to read through it several times to understand what on earth I was asking and how the answer was applicable. Seeing that it's been viewed over 6k times, perhaps it's worth (7 years later, sorry) to provide more context. Allow me to clarify the question: I was working for a banking software provider. We had various clients around the world and were rolling out a large update to our software which required a new column to be added to an existing table used by our software. This particular table was normally quite large depending on the size of the bank. The requirement was that when the column is first added, that a particular ID be assigned to all existing records, after which all new entries in the table will revert to a value of "0". So...during the testing phase we noticed that having the following in our upgrade script took nearly an hour to process 40m records: ``` ALTER TABLE myTable ADD NewColumnID int NOT NULL CONSTRAINT DF_Constraint DEFAULT 0 UPDATE myTable SET NewColumnID = 50 ``` The example above will add the new column and then update all existing records with NewColumnID = 50. This is what was taking nearly an hour on the hardware which it was running on. I appreciate that this will vary drastically depending on client's infrastructure. The reason for the question was to see if there was a faster way to accomplish the above. Allow me to clarify the answer: I completely understand why my answer makes no sense, but hopefully the following explanation will help: Instead of adding the column and then running an update query, you assign the value that you want all the existing records to inherit by creating a CONSTRAINT with a default value that is the value you want to update it with. The creation of the column will result in this value being automatically inserted: ``` ALTER TABLE myTable ADD CompanyID int NOT NULL CONSTRAINT DF_Constraint DEFAULT 1 (takes about 1min to complete) ``` It was essentially "killing two birds with one stone". This query completely in roughly 1min as apposed to an hour (executed on the same server). Now that the requirement for adding a new column with a default id = x (different for each client) for all existing records, the DEFAULT 0 constraint is restored so that all newly inserted records will assume a value of 0 if no value is passed. Hence the quote: Then just set the default value back to 0. Now the table will have CompanyID = 1 for all records. BOOM! Apologies...this was 7 years ago and this all seems really stupid now :) but who knows, maybe this could help others with stupid requirements that requires creative hacks :)!