分区列应该放在 SQL Server 上的主键中的什么位置? [英] Where should the partitioning column go in the primary key on SQL Server?

问题描述

使用 SQL Server 2005 和 2008.

Using SQL Server 2005 and 2008.

我有一个可能非常大的表格(可能有数亿行)，由以下列组成:

I've got a potentially very large table (potentially hundreds of millions of rows) consisting of the following columns:

CREATE TABLE (
    date SMALLDATETIME,
    id BIGINT,
    value FLOAT
)

在每日分区中的 date 列上进行分区.那么问题是主键应该在 date, id 还是 value, id 上?

which is being partitioned on column date in daily partitions. The question then is should the primary key be on date, id or value, id?

我可以想象 SQL Server 足够聪明，知道它已经在 date 上进行了分区，因此，如果我总是查询整整几天，那么我可以将它排在第二位首要的关键.或者我可以想象 SQL Server 将需要该列作为主键中的第一列才能获得分区的好处.

I can imagine that SQL Server is smart enough to know that it's already partitioning on date and therefore, if I'm always querying for whole chunks of days, then I can have it second in the primary key. Or I can imagine that SQL Server will need that column to be first in the primary key to get the benefit of partitioning.

任何人都可以对表格的键控方式提供一些见解吗?

Can anyone lend some insight into which way the table should be keyed?

推荐答案

按照标准做法，主键应该是唯一标识给定行的候选键.

As is the standard practice, the Primary Key should be the candidate key that uniquely identifies a given row.

您希望执行的操作称为对齐分区，这将确保主键也由您的分区键拆分并与适当的表数据一起存储.这是 SQL Server 中的默认行为.

What you wish to do, is known as Aligned Partitioning, which will ensure that the primary key is also split by your partitioning key and stored with the appropriate table data. This is the default behaviour in SQL Server.

有关完整的详细信息，请参阅参考分区SQL Server 2005 中的表和索引

For full details, consult the reference Partitioned Tables and Indexes in SQL Server 2005

这篇关于分区列应该放在 SQL Server 上的主键中的什么位置?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！