4

For example, if I have a BlogPosts table, and a PostCategory table, where the BlogPosts table has a FK field PostCategoryId. Would a relationship make the query faster, or is it more of a data quality thing?

What about when a join table is involved? Take the PostCategoryId field out of the BlogPosts table, and have a table "between" them called PostsInCategories, which has only 2 FK fields: BlogPostId and PostCategoryId, combined for form a PK.

Do relationships improve queries there?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Chaddeus
  • 13,134
  • 29
  • 104
  • 162
  • 1
    Creating and using additional tables with foreign keys slows down both selects and modifications. But this approach improves the design – zerkms Sep 07 '11 at 05:46
  • My understanding is that it will actually make it slower, but will help you maintain referential integrity. If you were going for absolute optimization, relationships will generally slow you down. – Stefan H Sep 07 '11 at 05:47
  • 1
    Duplicate of http://stackoverflow.com/questions/647193/database-relationships – Stefan H Sep 07 '11 at 05:56

1 Answers1

7

Foreign key relationships are about enforcing data integrity, e.g. making sure you don't have any "voodoo" child rows that don't have a parent row anymore, and so forth.

Foreign key relationships on their own do not boost your performance since SQL Server will not create any indices automatically for foreign keys.

For several reasons (boost performance when enforcing referential integrity and increase JOIN performance - see Kimberly Tripp's blog post on the topic) it is very much a recommended step to add indices to all foreign key fields in your tables - and adding those indices will speed up queries that will use those FK relationships.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • Thanks marc_s. I thought was the case, just wanted to be sure. – Chaddeus Sep 07 '11 at 06:27
  • "add indices to all foreign key fields " -- such blanket advice is inappropriate. The usual criteria will apply e.g. if the selectivity would be high (i.e. many duplicated values in the FK) them it may be better to omit the index in a high activity environment (i.e. many updates). – onedaywhen Sep 07 '11 at 08:10
  • Sure, I read the section of FKs but it doesn't address the counter view i.e. when omitting am index might be a good idea i.e. lacks balance. – onedaywhen Sep 07 '11 at 15:26
  • "I have not yet ever heard any reasoning why an index on a FK column would be a bad idea" -- I can help :) Coincidentally, I was looking at [this article](http://support.microsoft.com/kb/275561) earlier today. Background: Access (a.k.a. Jet) used to always add indexes to both sides of a FK but then they realised that this isn't always a good idea, so introduced the 'fast foreign keys' feature: – onedaywhen Sep 07 '11 at 15:29
  • Quote: "In cases where the values in a foreign key index are highly duplicated, using an index can be less efficient than simply scanning the table. maintaining such an index as records are inserted and deleted from the table, can degrade performance even further." Seems to me the same can be said of SQL Server, no? – onedaywhen Sep 07 '11 at 15:30
  • They both use b-trees but let's not debate that. Rather, I'll leave you with two thoughts: can you imagine an environment where updates far exceed queries and the cost of maintaining a highly selective index is not beneficial? And how about all those articles I've read about not creating an index if it would be highly selective: why do they not mention FKs as an exception to the rule? – onedaywhen Sep 08 '11 at 08:03
  • @onedaywhen I think this string of comments would have been better in a separate answer, or perhaps a blog post :) – El Ronnoco Aug 14 '12 at 08:54
  • 1
    @ElRonnoco: did you realize that this was a back and forth between marc_c and myself but the former subsequently deleted their comments? I left mine because they are still valid i.e. that blanket advice such as "add indices to all foreign key fields" is merely a rule of thumb and that there are exceptional circumstances e.g. high selectivity index in a high activity environment. – onedaywhen Aug 21 '12 at 07:53
  • My apologies, I did not realise that. Your point is valid though and I think would benefit from being consolidated into a single answer to the original post. Thanks :) – El Ronnoco Aug 21 '12 at 17:09