Best way to add a new column with an initial (but not default) value?

Adrian picture Adrian · Jun 25, 2009 · Viewed 34.9k times · Source

I need to add a new column to a MS SQL 2005 database with an initial value. However, I do NOT want to automatically create a default constraint on this column. At the point in time that I add the column the default/initial value is correct, but this can change over time. So, future access to the table MUST specify a value instead of accepting a default.

The best I could come up with is:

ALTER TABLE tbl ADD col INTEGER NULL
UPDATE tbl SET col = 1
ALTER TABLE tbl ALTER COLUMN col INTEGER NOT NULL

This seems a bit inefficient for largish tables (100,000 to 1,000,000 records).

I have experimented with adding the column with a default and then deleting the default constraint. However, I don't know what the name of the default constraint is and would rather not access sysobjects and put in database specific knowledge.

Please, there must be a better way.

Answer

Shannon Severance picture Shannon Severance · Jun 25, 2009

To add the column with a default and then delete the default, you can name the default:

ALTER TABLE tbl ADD col INTEGER NOT NULL CONSTRAINT tbl_temp_default DEFAULT 1
ALTER TABLE tbl drop constraint tbl_temp_default

This filled in the value 1, but leaves the table without a default. Using SQL Server 2008, I ran this and your code, of alter update alter and did not see any noticeable difference on a table of 100,000 small rows. SSMS would not show me the query plans for the alter table statements, so I was not able to compare the resources used between the two methods.