In SQL Server 2000/2005,

Can you really pressure the default value to become written to already established rows when adding a brand new column to some table without using NOT NULL around the new column?

You'll need two claims. First produce the column without null. Then alter 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

I doubt it.

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

The approach suggested by Microsoft is the following (obtained 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

I realize your question, but you're stating that for future records, NULL (unknown, indeterminate or whatever your semantics are) is appropriate (but when it's left off within an place, you will see a default), but that for the existing data, you will proceed and assign it the default.

I would need to look hard only at that situation and request your reason for extending its love to allow NULLs later on records whatsoever - given no historic records may have it, and there's a default in position for future records.