>>There is an ability to apply the change to the existing records, as I recall. Check closely ALTER TABLE syntax for the WITH CHECK clause.
>
>I do not see anything in there which negotiates with that:
>
>WITH CHECK | WITH NOCHECK
>
>Specifies whether the data in the table is or is not validated against a newly added or re-enabled FOREIGN KEY or CHECK constraint. If not specified, WITH CHECK is assumed for new constraints, and WITH NOCHECK is assumed for re-enabled constraints.
>
>If you do not want to verify new CHECK or FOREIGN KEY constraints against existing data, use WITH NOCHECK. This is not recommended except in rare cases. The new constraint will be evaluated in all future updates. Any constraint violations suppressed by WITH NOCHECK when the constraint is added may cause future updates to fail if they update rows with data that does not comply with the constraint.
>
>Constraints defined WITH NOCHECK are not considered by the query optimizer. These constraints are ignored until all such constraints are re-enabled using ALTER TABLE table CHECK CONSTRAINT ALL.

Check example H.

Adding a nullable column with default values
The following example adds a nullable column with a DEFAULT definition, and uses WITH VALUES to provide values for each existing row in the table. If WITH VALUES is not used, each row has the value NULL in the new column.

Copy

USE AdventureWorks2008R2 ; 
GO
CREATE TABLE dbo.doc_exf ( column_a INT) ;
GO
INSERT INTO dbo.doc_exf VALUES (1) ;
GO
ALTER TABLE dbo.doc_exf 
ADD AddDate smalldatetime NULL
CONSTRAINT AddDateDflt
DEFAULT GETDATE() WITH VALUES ;
GO
DROP TABLE dbo.doc_exf ;
GO

I meant WITH VALUES for default constraint.