Sql-server – Trusted Constraints and NOT FOR REPLICATION

constraintdatabase-designreplicationsql server

Version and Build: SQL Server 2005 SP4 (9.0.5000)

As a disclaimer of sorts, I am asking this question to generate some discussion and/or debate on the topic. I don't know if there is really a correct answer, or if the correct answer is a big fat "It Depends". As such, I will be pretty slow to accept an answer, unless definitive evidence is presented that cannot be countered by the opposing view.

The issue deals with the OBJECTPROPERTY CnstIsNotTrusted and the OBJECTPROPERTY CnstIsNotRepl, and how they relate. The CnstIsNotTrusted property is controlled with the WITH CHECK|NOCHECK while adding or checking a constraint. The CnstIsNotTrusted property means that the constraint was enabled without checking existing rows; therefore, the constraint may not be true for all rows. When true, the constraint cannot be used for query optimization. For example, I have a Person table with a Check Constraint on LastName ensuring LastName LIKE 'Mc%'. If CnstIsNotTrusted = 1 then if I search for LastName = 'Smith', the query plan will still need to scan/seek the index. If CnstIsNotTrusted = 0 then the execution plan won't even touch the table. This has been proven here:

https://sqlserverfast.com/blog/hugo/2007/03/can-you-trust-your-constraints/

The OBJECTPROPERTY CnstIsNotRepl (NOT FOR REPLICATION in DDL) enforces that the constraint will not be checked during replication synchronizations. More accurately stated, the publisher side will be checked, but the subscriber side will not be checked. This ensures that the same constraint isn't checked more than once. However, using NOT FOR REPLICATION will render your constraint untrusted.

My question is, is it worth the extra CPU cycles to not use NOT FOR REPLICATION, in order to sometimes receive a more efficient execution plan?

Best Answer

NOT FOR REPLICATION was indended for this scenario:

  1. You have a Order table with a FK to Person table.
  2. Order table is in publication no.1 and Person table is in publication no.2.
  3. The Order pub and Person pub will happen at different times. Therefore if some new records were in the Order table were replicated down to the subscriber and the Person table was missing the person that new Order was for, you don't want the replication insert to fail at the subscriber. Instead, it can be trusted that the new Person record will come down at some point because the FK was used at the Publisher.
  4. At the subscriber, if you insert an Order record, the FK will check that the Person record exists.

I would always stick with this policy and use NOT FOR REPLICATION only for that purpose of FK's relating to tables in different publications, and not for the purpose of query plan optimisation.

Related Question