在覆盖索引中包含主键好/无关紧要/坏? [英] good/doesn't matter/bad to include Primary Key in covering index?

问题描述

在覆盖索引中包含主键是good还是无关紧要或bad??

is is good or doesn't matter or bad to INCLUDE the primary key in a covering index??

CREATE NONCLUSTERED INDEX index_name_here ON dbo.table_name_here
(column_to_index_here) 
INCLUDE (primary_key_column,other_column_here)
WITH( STATISTICS_NORECOMPUTE=OFF, IGNORE_DUP_KEY=OFF, --<default junk from SSMS
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

我认为这无关紧要，因为无论如何 PK 都会在索引中.

I'm thinking it doesn't matter, since the PK will be in the index anyways.

编辑 - 澄清.
我的 primary_key_column 是集群的，但如果不是，您可以解释/提供信息.

EDIT - to clarify.
my primary_key_column is clustered, but you can explain/give info when it isn't too.

我将加入 column_to_index_here 列上的 dbo.table_name_here，然后加入 primary_key_column 和 上的其他表other_column_here.

I will be joining to dbo.table_name_here on the column_to_index_here column and will be then joining to other tables on primary_key_column and other_column_here.

推荐答案

如果您在 PK 上进行聚类，那么没关系.SQL Server 将忽略它，因为所有非聚集索引都包含该行的聚集索引键作为其定义的一部分.

If you are clustering on your PK, then it doesn't matter. SQL Server will disregard it since all non-clustered indexes contain the clustered index key for that row as part of their definition.

它不会在索引中使用任何额外的空间，但在定义中包含它是多余的.

It won't use any extra space in the index, but it's redundant to include it in the definition.

如果您的 PK 未包含在您的聚集索引中，那么仅当您需要检索该字段作为使用该索引的同一查询的一部分时才包含它.

If your PK isn't included in your clustered index, then only include it if you will need to retrieve that field as a part of the same query that uses the index.

还请记住，当您INCLUDE 索引中的字段时，它不在非叶节点中，即索引未按该值排序.

Also bear in mind when you INCLUDE a field in an index, it's NOT in the non-leaf nodes, i.e. the index is not sorted on that value.

这篇关于在覆盖索引中包含主键好/无关紧要/坏?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！