Force default value when adding a column to a table - SQL Server

Question

In SQL Server 2000/2005

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

Answer 1

You need two statements. First create a column with a null value. Then change the 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 2

, , NULL (, ) ( , ), , .

, NULL , , .

Answer 3

.

http://msdn.microsoft.com/en-us/library/ms190273(SQL.90).aspx

, Microsoft, ( URL )

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

Answer 4

ALTER TABLE {TABLENAME} ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE} [**WITH VALUES]**

WITH VALUES .

more about the MSDN link. https://msdn.microsoft.com/en-in/library/ms190273.aspx