At which stage does SqlBulkCopy check constraints?

Question

If SqlBulkCopyOptions.CheckConstraints option is set for SqlBulkCopy insert, does it check the constraints separately after each record, or after all records are inserted?

I have a check constraint that compares some columns to the parent row (through a UDF). I'm using MS Sql Server 2005. The documentation at http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopyoptions.aspx only says "Check constraints while data is being inserted."

In my tests it seems that foreign keys are not checked until the end, but check constraints are checked for each row before adding the next one. This sounds quite odd. I haven't found any documentation about this.

Answer 1

Not sure, if I understand this completely.

But, that could be because of transaction in place. It might check for foreign key constraints, when it is ready to commit, as against column constraints which can be checked independently (somewhat).