Force default value when adding column to table - SQL Server

Question

In SQL Server 2000/2005,

Is it possible to force the default value to be written to already existing rows when adding a new column to a table without using NOT NULL on the new column?

Answer 1

I doubt it.

http://msdn.microsoft.com/en-us/library/ms190273%28SQL.90%29.aspx

The approach recommended by Microsoft is as follows (taken from the url above)

UPDATE MyTable SET NullCol = N'some_value' WHERE NullCol IS NULL
ALTER TABLE MyTable ALTER COLUMN NullCOl NVARCHAR(20) NOT NULL

Answer 2

You need two statements. First create the column with not null. Then change the not null constraint to nullable

alter table mytable add mycolumn varchar(10) not null default ('a value')
alter table mytable alter column mycolumn varchar(10) null

Answer 3

I understand your question, but you are saying that for future records, NULL (unknown, indeterminate or whatever your semantics are) is acceptable (but if it is left off in an insert, there will be a default), but that for all the existing data, you are going to go ahead and assign it the default.

I would have to look hard at this situation and ask why you are even going to allow NULLs in future records at all - given none of the historical records will have it, and there is a default in place for future records.