在 SQL Server 2008 中，关系会使查询速度更快吗? [英] In SQL Server 2008, do relationships make queries faster?

问题描述

例如，如果我有一个 BlogPosts 表和一个 PostCategory 表，其中 BlogPosts 表有一个 FK 字段 PostCategoryId.关系会使查询更快，还是更多的是数据质量?

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?

当涉及连接表时呢?从 BlogPosts 表中取出 PostCategoryId 字段，并在它们之间有一个名为 PostsInCategories 的表，它只有 2 个 FK 字段:BlogPostId 和 PostCategoryId，组合成一个 PK.

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?

推荐答案

外键关系是关于强制数据完整性，例如确保您没有任何不再有父行的巫毒"子行，等等.

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.

外键关系本身不会提高您的性能，因为 SQL Server 不会自动为外键创建任何索引.

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

出于多种原因(在实施参照完整性时提高性能并提高 JOIN 性能 - 请参阅 Kimberly Tripp 关于该主题的博文) 非常推荐的步骤是向表中的所有外键字段添加索引- 添加这些索引将加速使用这些 FK 关系的查询.

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.

这篇关于在 SQL Server 2008 中，关系会使查询速度更快吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！