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:
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?