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

Question

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 1

You can do it in an insert trigger

Answer 2

I'd ALTER TABLE tbl ADD col INTEGER CONSTRAINT tempname DEFAULT 1 first,, and drop the explicitly named constraint after (presumably within a transaction).

Answer 3

If you add a default constraint when creating the table, you won't know what it is called. However, if you add a constraint with ALTER TABLE, you must name the constraint. In this case, you would be able to ALTER TABLE DROP CONSTRAINT (This applies to T-SQL, not sure about other databases.)

However, this would require you to CREATE TABLE with NULL column, ALTER TABLE to add the constraint, make the column NOT NULL, and finally DROP CONSTRAINT.

I don't believe an insert trigger would work as someone else mentioned, because your rows are already added.

I think the way you describe may, in fact, be the most efficient and elegant solution.

Answer 4

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.