SQL Server事务表总是有一个替代主键 [英] Should SQL Server transaction tables always have a surrogate primary key

问题描述

对于已有主键（4列的自然组合键）的大型事务表（1亿行，20 GB），它是否有助于添加标识列并使其成为主键？

当前主键（4列的自然复合主键）执行作业，但是我已经被告知您应该总是有一个代理键。 那么，可以通过创建标识列并将其作为主键来提高性能。

我使用的是SQL Server 2008 R2数据库。 / p>

EDIT：此事务表主要连接到定义表并用于填充报告。

编辑：如果我添加了一个代理键，它不会在任何连接中使用。将使用现有的关键字段。

EDIT：此表中不会有子表格

仅当：

• 您的子表格较大


• 您有非聚集索引

在每种情况下，表的PK（假定为聚类） / NC输入。

如果你只有非NC索引（也许一个），没有子表，你将实现

• 更宽的行（使用更多的数据页）


• 略小的B树空格）

...但是你仍然需要对当前4列的索引/约束=空间的增加。

如果你的四向键捕获父表键（听起来很可能），那么你会失去重叠的优势。这将被新的索引/约束覆盖。

不，你可能不想这么做。

我们丢弃了一个代理键bigint）在一个十亿行表上，并移动到实际的11路键，磁盘上的空间减少了65％+，因为一个更简单的结构（一个更少的索引，每页有更多的行等）

For a large table of transactions (100 million rows, 20 GB) that already has a primary key (a natural composite key of 4 columns), will it help performance to add an identity column and make that the primary key?

The current primary key (the natural composite primary key of 4 columns) does the job, but I have been told that you should always have a surrogate key. So, could improve performance by creating an identity column and making that the primary key?

I'm using SQL Server 2008 R2 database.

EDIT: This transaction table is mainly joined to definition tables and used to populate reports.

EDIT: If I did add a surrogate key, it wouldn't be used in any joins. The existing key fields would be used.

EDIT: There would be no child tables to this table

解决方案

Only if:

• you have child tables that are larger
• you have nonclustered indexes

In each of these cases, the PK (assumed clustered) of your table will be in each child entry/NC entry. So making the clustered key narrower will benefit.

If you have just non NC indexes (maybe one) and no child tables all you'll achieve is

• a wider row (more data pages used)
• a slightly smaller B-tree (which is a fraction of total space)

...but you'll still need an index/constraint on the current 4 columns anyway = an increase in space.

If your 4 way key capture parent table keys too (sounds likely) then you'd lose the advantage of overlap. This would be covered by the new index/constraint though.

So no, you probably don't want to do it.

We threw away a surrogate key (bigint) on a billion+ row table and moved to the actual 11-way key and reduced space on disk by 65%+ because of a simpler structure (one less index, slighty more rows per page etc)

这篇关于SQL Server事务表总是有一个替代主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！