Temporarily disable checking of constraints

Question

I'm bulk inserting rows into a database and I'm finding (through the query analyser) that the foreign key constraints are taking about 20% of the insertion time. I've tried creating a separate index on the tables containing just the key so that the check could hopefully just do an index seek rather than a clustered index seek but this didn't seem to work.

The next thing I was planning on trying was to disable to check constraints temporarily so I was wondering if there is a way to do this on a per transaction basis.

Failing that I will probably try the performance with the constraints removed as they are also enforced in our DataAccess layer so in theory are redundant.

This SO question contains a possible solution, but it's more permanent than temporary.

Answer 1

In order to get your new narrower index used for foreign key constraint validation you will need to drop and re-create the foreign keys.

You will then see in sys.foreign_keys that they are bound to the better index and it will use that for validation.

NB: This isn't documented anywhere but is definitely the way it appears to work!

Answer 2

Try this and do not forget bring them back! 8-) This is for CHECK constraints

DECLARE @sql NVARCHAR(MAX) = ''

SELECT @sql = @sql + N'ALTER TABLE [' + OBJECT_SCHEMA_NAME(fk.parent_object_id)+N'].['+OBJECT_NAME(fk.parent_object_id)+N'] NOCHECK CONSTRAINT ['+fk.NAME+N'];'+NCHAR(13)+NCHAR(10)
FROM sys.check_constraints fk

PRINT @sql

This is, almost the same, for FKeys

DECLARE @sql NVARCHAR(MAX) = ''

SELECT @sql = @sql + N'ALTER TABLE [' + OBJECT_SCHEMA_NAME(fk.parent_object_id)+N'].['+OBJECT_NAME(fk.parent_object_id)+N'] NOCHECK CONSTRAINT ['+fk.NAME+N'];'+NCHAR(13)+NCHAR(10)
FROM sys.foreign_keys fk

PRINT @sql

You know what to do with the scripts, yeah?

The best way I think - is to disable constraints right after BEGIN TRANSACTION and enable them back just before COMMIT

Hint - how to enable constraint without checking all the data on table:

CREATE TABLE test (id INT CONSTRAINT chk CHECK (id < 10))
GO 
INSERT test VALUES (1)
GO
ALTER TABLE test NOCHECK CONSTRAINT chk
GO
INSERT test VALUES (10)
GO
ALTER TABLE test WITH NOCHECK CHECK CONSTRAINT chk
GO
SELECT * FROM test
GO
INSERT test VALUES (10)

Only the last statement will generate an error Pay attention on WITH NOCHECK CHECK

But as Martin Smith noticed in the comments - it is the kind of worst practice.

So, the proper way is indexed fkey columns and enabling the constraints just before commit WITH CHECK CHECK, but if you sure about your data and the performance of bulk insert is much more important, than data purity, then you can do checks later, after the transaction already fixed, but this case cannot be called proper