Sometimes it's useful to disable one or more constraints on a table, do something significant, and then re-enable the constaint(s) after you're done. This is most often done to improve performance during a bulk load operation.

According to SQL Server Books Online, we can disable constraints using the ALTER TABLE statement.Here's an excerpt from SQL Server Books Online that describes it.

{ CHECK | NOCHECK } CONSTRAINT

    Specifies that constraint_name is enabled or disabled. This option can only be used with FOREIGN KEY and CHECK constraints. When NOCHECK is specified, the constraint is disabled and future inserts or updates to the column are not validated against the constraint conditions. DEFAULT, PRIMARY KEY, and UNIQUE constraints cannot be disabled.

ALL

    Specifies that all constraints are either disabled with the NOCHECK option or enabled with the CHECK option.

For more information, see the "Alter Table (Transact-SQL)" topic in SQL Server Books Online (http://technet.microsoft.com/en-us/library/ms190273.aspx).

Let's consider an example.The following script disables a single constraint, does something, and then re-enables the constraint using the ALTER TABLE statement.

--disable the CK_Customer_CustomerType constraint 
ALTER TABLE Sales.Customer NOCHECK CONSTRAINT CK_Customer_CustomerType

--do something

--enable the CK_Customer_CustomerType constraint 
ALTER TABLE Sales.Customer CHECK CONSTRAINT CK_Customer_CustomerType

The following example shows how to disable all constraints on a table.

--disable all constraints for the Sales.SalesOrderHeader table 
ALTER TABLE Sales.SalesOrderHeader NOCHECK CONSTRAINT ALL  

--do something  --enable all constraints for the Sales.SalesOrderHeader table 
ALTER TABLE Sales.SalesOrderHeader CHECK CONSTRAINT ALL  

Cheers!

Joe