Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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: http://sqlblog.com/blogs/hugo_kornelis/archive/2007/03/29/can-you-trust-your-constraints.aspx

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?

Thanks!

share|improve this question
add comment

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
I understand the purpose of NOT FOR REPLICATION, and I agree that there are times when it is not feasible to not use NOT FOR REPLICATION (probably too many nots, it is early). I suppose my question was more related to the times when your design does not require NOT FOR REPLICATION, but it was put in place to keep Checks and FKs from firing at the subscriber side as well. Great answer though. –  Matt M Aug 26 '11 at 10:55
    
I was really hoping for more discussion on this topic. Your answer only touches upon FKs, and my question was more related to CHECK constraints. It seems to me that using NOT FOR REPLICATION in complex replication environments for FKs makes sense, but for most simple CHECK constraints it can actually hurt performance. What are your thoughts regarding CHECK constraints and NOT FOR REPLICATION? –  Matt M Aug 30 '11 at 13:15
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.