Using WITH NO CHECK:
We have a table with 100 records.
1)Now we are creating a foreign Key Constraint by altering that table
ALTER TABLE [Sales].[SalesOrderDetailTemp]
WITH NOCHECK
ADD CONSTRAINT [FK_SalesOrderDetailTemp_SalesOrderHeader_SalesOrderID] FOREIGN KEY([SalesOrderID])
REFERENCES [Sales].[SalesOrderHeader] ([SalesOrderID])
Here by using NOCHECK option
-->Foreign key does not Checks for old 100 records exists in References table (Salesorderheader)
-->Instead of Foreign key will check for New record inserted.
Using NOCHECK:
ALTER TABLE [Sales].[SalesOrderDetailTemp]
NOCHECK CONSTRAINT [FK_SalesOrderDetailTemp_SalesOrderHeader_SalesOrderID]
This option Disables the Constraint of that Particular table