Check your constraints
Skrivet den 13 juli 2012 i T-SQL, Jan Nieminen, Level 200, SQL Server allmänt, sv
Sometimes you need to quickly import or delete large amounts of data. One way to make this quicker or easier is to disable all constraints, foreign keys and check constraints.
The following command will disable all constraints on a table:
ALTER TABLE [TableName] NOCHECK CONSTRAINT ALL;
Once data is imported you need to make sure the new data is valid according to the declarative rules in the database.
The following will enable all constraints on a table:
ALTER TABLE [TableName] CHECK CONSTRAINT ALL;
But wait! This command will only make sure new data is checked not the existing data!
To check already inserted data run the following:
ALTER TABLE [TableName] WITH CHECK CHECK CONSTRAINT ALL;
If you don't check existing data SQL Server will mark the constraint as not trusted and as a result of this it won't be able
to use the constraint to make a more efficient query plan.
If you want to list all check constraints and foreign key constraints that are not trusted you can query
sys.check_constraints and sys.foreign_keys views where is_not_trusted = 1.
SELECT * FROM sys.foreign_keys WHERE is_not_trusted = 1;
Bottom line: Make sure all your constraints are checked and trusted.