0

I do understand why replicated articles have not trusted FK constraints, but wanted to know if I force FK for trusted anyway, will query plan actually change?

1 Answer 1

0

long discussion here :- https://stackoverflow.com/questions/507179/does-foreign-key-improve-query-performance

Some but not all queries execution plans will be affected - you'd have to tell us more about what you're doing to get a specific answer

4
  • That thread doesn't seem to address replication Steve. Or did I miss anything? There are few cases when FKs actually will affect/help the exec plan, IMO. I would setup a test-case run a query to see if the NOT FOR REPLACATION option disabled these (rare) optimizations. Nov 1, 2021 at 18:12
  • Well question was confusingly mixing Disabled FKs and Not for replication. Can certainly affect DML query plans & certain select queries too Nov 1, 2021 at 21:22
  • @StephenMorris-Mo64 - Thanks for reply, I am trying to make all FKs on all published articles as trusted ones. So wanted to check if it has any benefit from performance perspective or not. The question is specific to FKs that are on tables participating in replication, enabled and are having NOT FOR RPELICATION in their definition. Hope this clears up confusion. :) Nov 2, 2021 at 10:30
  • NOT FOR REPLICATION constraints on a replication subscriber simply provide one additional avenue that a constraint can become not-trusted. It should have no effect on a publisher (unless it's a republisher). Nov 2, 2021 at 23:35

This site is temporarily in read-only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .