SQL Server 中是否需要主键？

Is a Primary Key necessary in SQL Server?

提问by roryok

This may be a pretty naive and stupid question, but I'm going to ask it anyway

这可能是一个非常幼稚和愚蠢的问题，但我还是要问

I have a table with several fields, none of which are unique, and a primary key, which obviously is.

我有一个包含多个字段的表，其中没有一个是唯一的，还有一个主键，显然是。

This table is accessed via the non-unique fields regularly, but no user SP or process access data via the primary key. Is the primary key necessary then? Is it used behind the scenes? Will removing it affect performance Positively or Negatively?

该表通过非唯一字段定期访问，但没有用户 SP 或进程通过主键访问数据。那么主键是必需的吗？是否在幕后使用？删除它会积极还是消极地影响性能？

采纳答案by Aaron Digulla

Necessary? No. Used behind the scenes? Well, it's saved to disk and kept in the row cache, etc. Removing will slightly increase your performance (use a watch with millisecond precision to notice).

必要的？不。在幕后使用？嗯，它被保存到磁盘并保存在行缓存中，等等。删除会稍微提高你的性能（使用毫秒精度的手表来注意）。

But ... the next time someone needs to create references to this table, they will curse you. If they are brave, they will add a PK (and wait for a long time for the DB to create the column). If they are not brave or dumb, they will start creating references using the business key (i.e. the data columns) which will cause a maintenance nightmare.

但是......下次有人需要创建对这个表的引用时，他们会诅咒你。如果他们勇敢，他们会添加一个PK（并等待很长时间让DB创建列）。如果他们不勇敢或不笨，他们将开始使用业务键（即数据列）创建引用，这将导致维护噩梦。

Conclusion: Since the cost of having a PK (even if it's not used ATM) is so small, let it be.

结论：既然拥有PK（即使不使用ATM）的成本如此之低，就这样吧。

回答by Remus Rusanu

Do you have any foreign keys, do you ever join on the PK?

你有任何外键吗，你有没有参加过 PK？

If the answer to this is no, and your app never retrieves an item from the table by its PK, and no query ever uses it in a where clause, therefore you just added an IDENTITY column to have a PK, then:

如果答案是否定的，并且您的应用程序从不通过其 PK 从表中检索项目，并且没有查询在 where 子句中使用它，因此您只是添加了一个 IDENTITY 列以获得一个 PK，那么：

• the PK in itself adds no value, but does no damage either
• the fact that the PK is very likely the clustered index too is .. it depends.

• PK本身没有任何价值，但也不会造成伤害
• PK 很可能是聚集索引的事实也是..这取决于。

If you have NC indexes, then the fact that you have a narrow artificial clustered key (the IDENTITY PK) is helpful in keeping those indexes narrow (the CDX key is reproduced in every NC leaf slots). So a PK, even if never used, is helpful if you have significant NC indexes.

如果您有 NC 索引，那么您拥有一个狭窄的人工聚集密钥（IDENTITY PK）这一事实有助于保持这些索引的狭窄（CDX 密钥在每个 NC 叶槽中复制）。因此，如果您有重要的 NC 索引，即使从未使用过 PK，也会很有帮助。

On the other hand, if you have a prevalent access pattern, a certain query that outweighs all the other is frequency and importance, or which is part of a critical time code path (eg. is the query run on every page visit on your site, or every second by and app etc) then that query is a good candidate to dictate the clustered key order.

另一方面，如果您有一个普遍的访问模式，那么某个查询在频率和重要性方面胜过其他所有查询，或者它是关键时间代码路径的一部分（例如，查询是否在您网站上的每次页面访问时运行） ，或每秒由和应用程序等），那么该查询是决定聚集键顺序的一个很好的候选者。

And finally, if the table is seldom queried but often written to then it may be a good candidate for a HEAP (no clustered key at all) since heaps are so much better at inserts. See Comparing Tables Organized with Clustered Indexes versus Heaps.

最后，如果表很少被查询但经常被写入，那么它可能是 HEAP 的一个很好的候选者（根本没有聚集键），因为堆在插入时要好得多。请参阅比较使用聚集索引组织的表与堆。

回答by SQLMenace

The primary key is behind the scenes a clustered index(by default unless generated as a non clustered index) and holds all the data for the table. If the PK is an identity column the inserts will happen sequentially and no page splits will occur.

主键在幕后 a clustered index（默认情况下，除非作为非聚集索引生成）并保存表的所有数据。如果 PK 是标识列，则插入将按顺序发生并且不会发生页面拆分。

But if you don't access the id column at all then you probably want to add some indexes on the other columns. Also when you have a PK you can setup FK relationships

但是，如果您根本不访问 id 列，那么您可能希望在其他列上添加一些索引。此外，当您有 PK 时，您可以设置 FK 关系

回答by onedaywhen

In the logical model, a table must have at least one key. There is no reason to arbitarily specify that one of the keys is 'primary'; all keys are equal. Although the concept of 'primary key' can be traced back to Ted Codd's early work, the mistake was picked up early on has long been corrected in relational theory.

在逻辑模型中，一张表必须至少有一个键。没有理由武断地指定其中一个键是“主要的”；所有的键都是平等的。虽然“主键”的概念可以追溯到 Ted Codd 的早期工作，但这个错误很早就在关系理论中得到纠正。

Sadly, PRIMARY KEYfound it's way into SQL and we've had to live with it ever since. SQL tables can have duplicate rows and, if you consider the resultset of a SELECTquery to also be a table, then SQL tables can have duplciate rows too. Relational theorists dislike SQL a lot. However, just because SQL lets you do all kinds of wacky non-relational things, that doesn't mean that you have to actually do them. Is is good practise to ensure that every SQL table has at least one key.

可悲的是，PRIMARY KEY发现它进入了 SQL，从那时起我们就不得不忍受它。SQL 表可以有重复的行，如果您认为SELECT查询的结果集也是一个表，那么 SQL 表也可以有重复的行。关系理论家非常不喜欢 SQL。但是，仅仅因为 SQL 允许您执行各种古怪的非关系操作，并不意味着您必须实际执行这些操作。确保每个 SQL 表至少有一个键是一种很好的做法。

In SQL, using PRIMARY KEYon its own has implications e.g. NOT NULL, UNIQUE, the table's default reference for foreign keys. In SQL Server, using PRIMARY KEYon its own has implications e.g. the table's clustered index. However, in all these cases, the implicit behaviour can be made explicit using specific syntax.

在 SQL 中，单独使用PRIMARY KEY具有含义，例如NOT NULL，UNIQUE表的外键默认引用。在 SQL Server 中，单独使用PRIMARY KEY会产生影响，例如表的聚集索引。但是，在所有这些情况下，可以使用特定语法使隐式行为显式。

You can use UNIQUE(constraint rather than index) and NOT NULLin combination to enforce keys in SQL. Therefore, no, a primary key (or even PRIMARY KEY) is not necessary in SQL Server.

您可以使用UNIQUE(constraint 而不是索引) 和NOT NULL组合来强制执行 SQL 中的键。因此，不，PRIMARY KEYSQL Server 中不需要主键（甚至）。

回答by HLGEM

I would never have a table without a primary key. Suppose you ever need to remove a duplicate - how would you identify which one to remove and which to keep?

我永远不会有没有主键的表。假设您曾经需要删除重复项 - 您将如何确定要删除的内容和保留的内容？

回答by Mitch Wheat

A primary key is really a property of your domain model, and it uniquely identifies an instance of a domain object.

主键实际上是域模型的一个属性，它唯一标识了域对象的实例。

Having a clustered index on a montonically increasing column (such as an identity column) will mean page splits will not occur, BUT insertions will unbalance the index over time and therefore rebuilding indexes needs to be done regulary (or when fragmentation reaches a certain threshold).

在单调递增的列（例如标识列）上拥有聚集索引意味着不会发生页面拆分，但插入会随着时间的推移使索引不平衡，因此需要定期重建索引（或当碎片达到某个阈值时） .

I have to have a very good reason to create a table without a primary key.

我必须有一个很好的理由来创建一个没有主键的表。

回答by Thomas Weller

A PK is not necessary.

不需要PK。

But you should consider to place a non-unique index on the columns that you use for querying (i.e. that appear in the WHERE-clause). This will considerably boost lookup performance.

但是您应该考虑在用于查询的列（即出现在 WHERE 子句中）上放置一个非唯一索引。这将大大提高查找性能。

回答by Tom Gullen

The primary key when defined will help improve performance within the database for indexing and relationships.

定义的主键将有助于提高数据库中索引和关系的性能。

I always tend to define a primary key as an auto incrementing integer in all my tables, regardless of if I access it or not, this is because when you start to scale up your application, you may find you do actually need it, and it makes life a lot simpler.

我总是倾向于将主键定义为所有表中的自动递增整数，无论​​我是否访问它，这是因为当您开始扩展应用程序时，您可能会发现您确实需要它，并且它让生活简单很多。

回答by ProfessionalAmateur

If you are accessing them via non-key fields the performance probably will not change. However it might be nice to keep the PK for future enhancements or interfaces to these tables. Does your application only use this one table?

如果您通过非关键字段访问它们，性能可能不会改变。但是，保留 PK 以供将来增强或这些表的接口可能会很好。您的应用程序是否只使用这张表？

回答by marr75

As SQLMenace said, the clustered index is an important column for the physical layout of the table. In addition, having a clustered index, especially a well chosen one on a skinny column like an integer pk, actually increases insert performance.

正如SQLMenace所说，聚集索引是表物理布局的重要列。此外，拥有聚集索引，尤其是在像整数 pk 这样的瘦列上精心选择的索引，实际上可以提高插入性能。