MCTS 70-433 Constraint WITH NOCHECK

In exception circumstances you may want to temporarily disable a constraint:

alter table employee NOCHECK CONSTRAINT Age_Check;     <<>

alter table employee CHECK CONSTRAINT Age_Check;           <<>

Turning off check constraints is an old and well established process, not much new here. Naturally the fun starts with disabling check constraints not when you turn off, but when you want to re-enable them but have violating entries .. 

Forcing a FOREIGN KEY Constraint by Using WITH NOCHECK

When a FOREIGN KEY constraint is added to an existing column or columns in the table, by default, the Database Engine examines the existing data in the columns to make sure that all values, except NULL, exist in the columns of the referenced PRIMARY KEY or UNIQUE constraint. However, by specifying WITH NOCHECK, the Database Engine can be prevented from checking the data in the column against the new constraint and made to add the new constraint regardless of the data in the column. The WITH NOCHECK option is useful when the existing data already meets the new FOREIGN KEY constraint, or when a business rule requires the constraint to be enforced only from this point forward.

http://msdn.microsoft.com/en-us/library/ms177463.aspx