sql server 2012 - check constraint -

i'm using sql server 2012 management studio. below have write. appreciate help.

alter table [tablename] check add constraint [constraintname] check   (([column]='a' or [column]='b' or [column]='c' or [column]='d')) 

but when generate script through sql server 2012 management studio, getting below statements..

alter table [tablename] nocheck add constraint [constraintname] check   (([column]='a' or [column]='b' or [column]='c' or [column]='d')) go alter table [tablename] check constraint [constraintname] 

why generated script first constructed nocheck , apply check on table? happen if create check constraint without nocheck?

the generated script uses nocheck, constraint created, if values in table don't comply. better automatically generated script, because ensures database schema created or amended correctly.

i thought @ first, second statement in generated script ensure query optimizer consider constraint. (see reference below.) however, on further investigation, seems second statement doesn't have effect -- see second answer.

when doing manually, using check, can see straight away statement has failed , take corrective action. run statement again.

if use nocheck option, might not notice data invalid. personally, use check when doing alter table add constraints. books online says using nocheck not recommended, except in rare cases -- see references below.

to demonstrate difference, let's first create table , insert values.

create table dbo.t (     id int not null identity(1,1),     [column] nchar(1) not null );  insert dbo.t ([column])     values (n'a'), (n'b'), (n'c'), (n'e'); 

try alter table , add constraint, specifying check. fails because of data in table not comply constraint.

alter table dbo.t     check     add constraint ck_column check ([column] in (n'a', n'b', n'c', n'd')); 

let's see if constraint has been created, using sys.check_constraints system catalog view. shows constraint has not been created.

select *     sys.check_constraints     [parent_object_id] = object_id(n'dbo.t'); 

try again, time specifying nocheck. time completes successfully. however, there no warning data in table invalid, might undesirable.

alter table dbo.t     nocheck     add constraint ck_column check ([column] in (n'a', n'b', n'c', n'd')); 

and can verify check constraint has been created.

see books online > alter table (transact-sql): https://msdn.microsoft.com/en-gb/library/ms190273.aspx > check | nocheck

if not want verify new check or foreign key constraints against existing data, use nocheck. not recommend doing this, except in rare cases. new constraint evaluated in later data updates. constraint violations suppressed nocheck when constraint added may cause future updates fail if update rows data not comply constraint.

the reason second automatically generated statement revealed here:

the query optimizer not consider constraints defined nocheck. such constraints ignored until re-enabled using alter table table check check constraint all.