blocks|key|452151|text|--编辑:基于问题编辑的，添加标识/代理键可能不是解决这个问题的方法。|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|452152|--原来的答案.|452153|性能改进的一种情况是使用联接时和有子表时。|452154|在没有代理键的情况下，您必须将所有th4+4键复制到子表中，并在4列上加入。|452155|t_parent
-------------
col1,
col2,
col3,
col4,
col5,
constraint+pk_t_parent+primary+key+(col1,col2,col3,col4)

t_child
----------
col1,
col2,
col3,
col4,
col7,
col8,
constraint+pk_t_child+primary+key+(col1,col2,col3,col4,+col5),
constraint+fk_parent_child+foreign+key+(col1,+col2,+col3,+col4)+references
+++++++++++++++++++++++++++++++++t_parent+((col1,+col2,+col3,+col4))|code-block|syntax|javascript|452156|联接将包括所有4列。|452157|select+t2.*
++from+t_parent+t1,+t_child+t2
++where+(t1.col1+=+t2.col1+and+
+++++++++t1.col2+=+t2.col2+and+
+++++++++t1.col3+=+t2.col3+and+
+++++++++t1.col4+=+t2.col4
++++++++)|452158|如果您使用代理键并在4列(现在是主键的一部分)上创建一个唯一的约束，那么它将是有效的，而且数据仍将像以前一样得到验证。|452159|entityMap^0|0|C|0|0|8|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@$9|Z|A|10|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|11|8|@$9|12|A|13|B|C]]|D|@]|E|$]]|$1|H|3|I|5|6|7|14|8|@]|D|@]|E|$]]|$1|J|3|K|5|6|7|15|8|@]|D|@]|E|$]]|$1|L|3|M|5|N|7|16|8|@]|D|@]|E|$O|P]]|$1|Q|3|R|5|6|7|17|8|@]|D|@]|E|$]]|$1|S|3|T|5|N|7|18|8|@]|D|@]|E|$O|P]]|$1|U|3|V|5|6|7|19|8|@]|D|@]|E|$]]|$1|W|3|-4|5|6|7|1A|8|@]|D|@]|E|$]]]|X|$]]

---EDIT:
Based on the edits to the question, adding an identity/surrogate key might not be the solution to this problem.

--Original Answer.

One case of performance improvement would be when you use joins and when you have child tables.

In the absence of surrogate keys, you would have to replicate all th4 4 keys to the child table and join on the 4 columns. 

<pre><code>t_parent
-------------
col1,
col2,
col3,
col4,
col5,
constraint pk_t_parent primary key (col1,col2,col3,col4)

t_child
----------
col1,
col2,
col3,
col4,
col7,
col8,
constraint pk_t_child primary key (col1,col2,col3,col4, col5),
constraint fk_parent_child foreign key (col1, col2, col3, col4) references
 t_parent ((col1, col2, col3, col4))
</code></pre>

The joins will include all the 4 columns..

<pre><code>select t2.*
 from t_parent t1, t_child t2
 where (t1.col1 = t2.col1 and 
 t1.col2 = t2.col2 and 
 t1.col3 = t2.col3 and 
 t1.col4 = t2.col4
 )
</code></pre>

If you use a surrogate key and create a unique constraint on the 4 columns (which are now part of the primary key), it will be both efficient and the data would still be validated as before.

blocks|key|452161|text|仅仅添加一个标识列并为其添加一个新的约束和索引是不可能提高性能的。该表将更大，因此扫描和寻找可能需要更长的时间。还将有更多的索引需要更新。当然，这一切都取决于你在衡量.以及在添加新列时是否打算对代码或数据库进行其他更改。添加身份列而不做其他操作可能是不明智的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|452162|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Just adding an IDENTITY column and adding a new constraint and index for it is very unlikely to improve performance. The table will be larger and therefore scans and seeks could take longer. There will also be more indexes to update. Of course it all depends what you are measuring the performance of... and whether you intend to make other changes to code or database when you add the new column. Adding an IDENTITY column and doing nothing else would probably be unwise.

blocks|key|1936878|text|只有下列情况下：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1936879|1936880|您有更大的|unordered-list-item|1936881|子表，您有非聚集索引|1936882|1936883|在每种情况下，表的PK+(假定集群)将位于每个子条目/NC条目中。因此，使集群键更窄将受益。|1936884|如果您只有非NC索引(可能是一个)，并且没有子表，那么您所能实现的就是|1936885|1936886|一个更宽的行(使用了更多的数据页)|1936887|，一个稍微小一点的B树(占总空间的一小部分)|1936888|1936889|...but，您仍然需要当前4列上的索引/约束，不管怎么说，都是空间的增加。|1936890|如果您的4路键也捕获父表键(听起来很可能)，那么您将失去重叠的优势。不过，新的索引/约束将涵盖这一点。|1936891|所以不，你可能不想这么做。|1936892|我们丢弃了billion%2B行表上的代理键(bigint)，移动到实际的11路键，并通过65%25%2B减少了磁盘上的空间，因为它的结构更简单(少一个索引，每页少一些行等等)。|1936893|entityMap^0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|12|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|13|8|@]|9|@]|A|$]]|$1|C|3|D|5|E|7|14|8|@]|9|@]|A|$]]|$1|F|3|G|5|E|7|15|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|16|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|17|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|18|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|19|8|@]|9|@]|A|$]]|$1|N|3|O|5|E|7|1A|8|@]|9|@]|A|$]]|$1|P|3|Q|5|E|7|1B|8|@]|9|@]|A|$]]|$1|R|3|-4|5|6|7|1C|8|@]|9|@]|A|$]]|$1|S|3|T|5|6|7|1D|8|@]|9|@]|A|$]]|$1|U|3|V|5|6|7|1E|8|@]|9|@]|A|$]]|$1|W|3|X|5|6|7|1F|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|6|7|1G|8|@]|9|@]|A|$]]|$1|10|3|-4|5|6|7|1H|8|@]|9|@]|A|$]]]|11|$]]

Only if:

<ul>
<li>you have child tables that are larger</li>
<li>you have nonclustered indexes</li>
</ul>

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

<ul>
<li>a wider row (more data pages used)</li>
<li>a slightly smaller B-tree (which is a fraction of total space)</li>
</ul>

...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)

blocks|key|1936922|text|影响性能的一个地方是自然键中数据的变化。这一变化将不得不公布给所有的儿童记录。例如，假设其中一个字段是公司名称，公司更改了它们的名称，那么所有相关的记录，可能有数百万条，都必须更改，但是如果使用代理密钥，则只需要更改一条记录。整数联接往往更快(通常比4列联接要快得多)，而编写要联接的代码通常也更快。然而，另一方面，拥有重要的四个字段可能意味着连接不像通常那样需要。插入性能，接受轻微命中，以及代理键必须生成和索引。通常情况下，这是一个很小的打击，以至于不被注意到，但可能性是存在的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1936923|四列自然键通常并不像您所认为的那样是唯一的，因为数据的列数会随着时间的推移而变化。虽然它现在是独一无二的，但随着时间的推移，它会是独一无二的吗？如果您已经使用了一个代理键和一个唯一的索引关于自然键，后来发现它不是唯一的，那么你所要做的就是删除唯一的索引。如果是PK，并且有子表，则必须完全重新设计数据库。|1936924|只有您可以决定，如果这些考虑因素中的任何一个影响到您的特定数据需求，代理键对某些应用程序更好，而对于其他应用程序则更糟。|1936925|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

One place where performance is hurt is on the change of the data in the natural key. The change woudl then have to promulgate to all the child records. For instance, suppose one of those fields was company name and the company changed their name, then all the related records, and there could be millions of them, would have to change but if you used a surrogate key, only one record would have to change. Integer joins tend to be faster (generally much faster than 4 column joins) and wrting the code to join is generally faster as well. However, on the other hand, having the vital four fields may mean the join isn't needed as often. Insert performanc ewilltake a slight hit as well as the surrogate key has to be generated and indexed. Usually this is so small a hit as to be unnoticalbe but the possibility is there. 

A four column natural key is often not a unique as you think it will be because that number of columns the data tends to change over time. While it is unique now, will it be unique over time? If you have used a surrogate key and a unique index onteh natural key and it turns out later not to unique, then all you have to do is drop the unique index. If it is the PK and there are child tables, you have to totally redesign your database. 

Only you can decide which if any of these considerations affects your specific data needs, surrogate keys are better for some applications and worse for others.

blocks|key|1917400|text|考虑到您的编辑，尽管问题引发了所有的讨论，我还是建议在这个表中添加一个标识列会带来更大的伤害而不是好处。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1917401|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Given your edits, and all the conversation the question has sparked notwithstanding, I would suggest that adding an IDENTITY column to this table will create a lot more harm than benefit.

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

Should SQL Server transaction tables always have a surrogate primary key

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

对于已经有主键(由4列组成的自然组合键)的大型事务表(1亿行，20 GB)，添加标识列并使其成为主键对性能有帮助吗？当前的主键(4列的自然复合主键)完成了这项工作，但是有人告诉我，您应该始终有一个代理键。因此，可以通过创建标识列并使其成为主键？来提高性能。我正在使用Server 2008 R2数据库。编辑:此事务表主要...

问Server事务表是否应该始终具有代理项主键
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Server事务表是否应该始终具有代理项主键EN