The information in this article applies to:
SUMMARYSQL Server version 6.5 has the ability to disable deferred constraint checking. Deferred constraints are described in this article; examples showing the effects of disabling them with the use of SET Disable_Def_Cnst_Chk ON are also provided. MORE INFORMATION
By default, constraints are checked during the execution of a statement
for each row that the statement affects and then perhaps again after all
rows have been modified. This is necessary because an UPDATE statement
might make a change to a row that by itself might seem to violate a
constraint, but when viewed with all of the subsequent changes to other
rows (by the same statement execution), the table is left in a
consistent state. As constraints are checked for each row, any apparent
violations are marked for rechecking and "deferred." Once all rows have
been updated, all marked rows have their constraints rechecked (this is
"deferred constraint checking"); if any violations now occur, the
execution of the statement fails; and if no violation occurs, the statement
succeeds.
This gives the following table:
When the following UPDATE statement is executed, if the rows were considered individually, the UPDATE should fail for every row in the table (because of a missing primary key for its mgr_id value) except for CEO; but even that should fail because that would leave PRES without a primary key for its manager. However, the UPDATE statement succeeds without any constraint errors because it uses deferred constraint checking.
Gives the result:
For an operation on a large table, deferred constraint checking may cause a loss in performance. An UPDATE may affect thousands of rows only to find that many of the constraint violations that it found as it was checking each row are still violations once it does the deferred constraint checking. Of course, all it has to find is the first failure in the deferred phase, and then it will have to cancel the statement and rollback all of the changes. It may take quite a long time to perform the operation and all of the checking, plus the rolling back (the system is optimized for going forward, not rolling back). It might have been better if the first failed constraint it encountered had caused the entire operation to fail rather than perform all that additional processing only to confirm that it could have failed earlier. DISABLING DEFERRED CONSTRAINT CHECKINGIn SQL Server 6.5, it is possible to disable deferred constraint checking by using the command:SET DISABLE_DEF_CNST_CHK ON With this option on, the reverse of the above operation will fail (as would the original operation):
Notice that the UPDATE fails even though it is valid and would leave the table in a consistent state with all constraints satisfied. On a very large table, the apparent speed will be much improved, although it may fail when it actually should not have. The deferred constraint behavior depends on the current setting of Disable_Def_Cnst_Chk when the operation is performed, not what its value was when the table or constraint was created. The behavior with deferred constraint checking shut off is similar to a poorly written trigger that only validates operations a row at a time rather than the result of the operation as a whole. ANSI specifies that deferred constraint checking should occur (the Disable_Def_Cnst_Chk option should be OFF). Higher levels of the ANSI standard (not implemented in SQL Server 6.5) allow constraints to be specified with terms like 'Initially Deferred', 'Initially Immediate', and '[Not] Deferrable' to customize this behavior at the constraint-level.
Keywords : kbusage SSrvInst SSrvProg |
Last Reviewed: March 25, 1999 © 2000 Microsoft Corporation. All rights reserved. Terms of Use. |