SQL 主键与聚集索引的关系
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15051869/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Relationship of Primary Key and Clustered Index
提问by F11
Can a TABLE have primary key without Clustered Index?
没有聚集索引的表可以有主键吗？
and Can a TABLE have Clustered Index without primary key?
表可以有没有主键的聚集索引吗？
Can anybody briefly tell me the relationship of primary key and clustered index?
谁能简单介绍一下主键和聚集索引的关系？
回答by Neville Kuyt
A primary key is a logicalconcept - it's the unique identifier for a row in a table. As such, it has a bunch of attributes - it may not be null, and it must be unique. Of course, as you're likely to be searching for records by their unique identifier a lot, it would be good to have an index on the primary key.
主键是一个逻辑概念 - 它是表中行的唯一标识符。因此，它有一堆属性——它不能为空，而且必须是唯一的。当然，由于您可能经常通过唯一标识符搜索记录，因此最好在主键上建立索引。
A clustered index is a physicalconcept - it's an index that affects the order in which records are stored on disk. This makes it a very fast index when accessing data, though it may slow down writes if your primary key is not a sequential number.
聚集索引是一个物理概念 - 它是影响记录在磁盘上存储顺序的索引。这使它在访问数据时成为一个非常快的索引，但如果您的主键不是序列号，它可能会减慢写入速度。
Yes, you can have a primary key without a clustered index - and sometimes, you may want to (for instance when your primary key is a combination of foreign keys on a joining table, and you don't want to incur the disk shuffle overhead when writing).
是的，您可以拥有一个没有聚集索引的主键 - 有时，您可能想要（例如，当您的主键是连接表上的外键组合，并且您不想招致磁盘洗牌开销写作时）。
Yes, you can create a clustered index on columns that aren't a primary key.
是的，您可以在不是主键的列上创建聚集索引。
回答by Andomar
A table can have a primary key that is not clustered, and a clustered table does not require a primary key. So the answer to both questions is yes.
一个表可以有一个非聚簇的主键，聚簇表不需要主键。所以这两个问题的答案都是肯定的。
A clustered index stores all columns at the leaf level. That means a clustered index contains all data in the table. A table without a clustered index is called a heap.
聚集索引在叶级别存储所有列。这意味着聚集索引包含表中的所有数据。没有聚集索引的表称为堆。
A primary key is a unique index that is clustered by default. By default means that when you create a primary key, if the table is not clustered yet, the primary key will be created as a clustered unique index. Unless you explicitly specify the nonclusteredoption.
主键是默认情况下聚集的唯一索引。默认情况下，当您创建主键时，如果表尚未聚簇，则主键将创建为聚簇唯一索引。除非您明确指定该nonclustered选项。
An example, where t1has a nonclustered primary key, and t2is not clustered but has a primary key:
一个例子，其中t1有一个非集群主键，并且t2没有集群但有一个主键：
create table t1 (id int not null, col1 int);
alter table t1 add constraint PK_T1 primary key nonclustered (id);
create clustered index IX_T1_COL1 on t1 (col1);
create table t2 (id int not null, col1 int);
alter table t2 add constraint PK_T2 primary key nonclustered (id);
回答by Branko Dimitrijevic
First of all, take a look at Index-Organized Tables and Clustered Indexes. Actually, I recommend reading the whole Use the Index Luke!site from the beginning until you reach the clustering topic to really understand what's going on.
首先，看看索引组织表和聚集索引。实际上，我建议阅读整个使用索引卢克！网站从一开始直到您到达聚类主题以真正了解正在发生的事情。
Now, to your questions...
现在，对于你的问题...
Can a TABLE have primary key without Clustered Index?
没有聚集索引的表可以有主键吗？
Yes, use NONCLUSTERED keyword when declaring your primary key to make a heap-based table. For example:
是的，在声明主键以创建基于堆的表时使用 NONCLUSTERED 关键字。例如：
CREATE TABLE YOUR_TABLE (
YOUR_PK int PRIMARY KEY NONCLUSTERED
-- Other fields...
);
This is unfortunate, since a lot of people seem to just accept the default (which is CLUSTERED), even though in many cases a heap-based table would actually be better (as discussed in the linked article).
这是不幸的，因为很多人似乎只接受默认值（即 CLUSTERED），即使在许多情况下基于堆的表实际上会更好（如链接文章中所述）。
and Can a TABLE have Clustered Index without primary key?
表可以有没有主键的聚集索引吗？
Unlike some other DBMSes, MS SQL Server will let you have a clustering index that is different from primary key, or even without having the primary key at all.
与其他一些 DBMS 不同，MS SQL Server 将让您拥有一个不同于主键的聚集索引，甚至根本没有主键。
The following example creates a clustering index separate from the PK, that has a UNIQUE constraint on top of it, which is what you'd probably want in most cases:
下面的示例创建了一个与 PK 分开的集群索引，它上面有一个 UNIQUE 约束，这在大多数情况下可能是您想要的：
CREATE TABLE YOUR_TABLE (
YOUR_PK int PRIMARY KEY,
YOUR_CLUSTERED_KEY int NOT NULL UNIQUE CLUSTERED
-- Other fields...
);
If you choose a non-unique clustering index (using CREATE CLUSTERED INDEX ...), MS SQL Server will automatically make it unique by adding a hidden field to it.
如果您选择非唯一的聚集索引（使用CREATE CLUSTERED INDEX ...），MS SQL Server 将通过向其添加隐藏字段来自动使其唯一。
Please note that the benefits of clustering are most visible for range scans. If you use a clustering index that doesn't "align" with range scans done by your client application(s) (such as when over-relying on the hidden column mentioned above, or clustering on a surrogate key), you are pretty much defeating the purpose of clustering.
请注意，聚类的好处在范围扫描中最为明显。如果您使用的集群索引与客户端应用程序执行的范围扫描不“对齐”（例如过度依赖上面提到的隐藏列，或在代理键上集群时），您几乎违背了聚类的目的。
Can anybody briefly tell me the relationship of primary key and clustered index?
谁能简单介绍一下主键和聚集索引的关系？
Under MS SQL Server, primary key is also clustered by default. You can change that default, as discussed above.
在 MS SQL Server 下，主键默认也是集群的。如上所述，您可以更改该默认值。
回答by Sepster
Answers taken from MSDN Using Clustered Indexes
Can a TABLE have primary key without Clustered Index?- Yes.
没有聚集索引的表可以有主键吗？- 是的。
Can a TABLE have Clustered Index without primary key?- Yes.
TABLE可以有没有主键的聚集索引吗？- 是的。
A Primary Keyis a constraintthat ensures uniqueness of the values, such that a row can always be identified specifically by that key.
甲主键是一个约束，以确保唯一性的值，使得一个行总是可以特别地由键标识。
An indexis automatically assigned to a primary key (as rows are often "looked up" by their primary key).
的索引被自动分配给主键（如行通常“抬头”可以通过主键）。
A non-clustered indexis a logical ordering of rows, by one (or more) of its columns. Think of it as effectively another "copy" of the table, ordered by whatever columns the index is across.
阿非聚集索引是行的逻辑顺序，由它的列的一个（或多个）。将其视为有效的表的另一个“副本”，按索引所跨越的任何列排序。
A clustered indexis when the actualtable is physically ordered by a particular column. A table will not always have a clustered index (ie while it'll be physically ordered by something, that thing might be undefined). A table cannot have more than one clustered index, although it can have a single composite clustered index (ie the table is physically ordered by eg Surname, Firstname, DOB).
一个聚集索引是当实际的表在物理上按特定的列进行排序。一个表并不总是有一个聚集索引（即，虽然它会被某物物理排序，但该物可能是未定义的）。一张表不能有多个聚簇索引，尽管它可以有一个复合聚簇索引（即该表按姓氏、名字、DOB 等物理排序）。
The PK is often (but not always) a clustered index.
PK 通常（但不总是）是聚集索引。
回答by robotj
For what it may be worth, in MS SQL Server all columns in the primary key must be defined as NOT Null, while creating unique clustered index does not require this. Not sure about other DB systems though.
值得一提的是，在 MS SQL Server 中，主键中的所有列都必须定义为 NOT Null，而创建唯一聚集索引不需要这样做。虽然不确定其他数据库系统。
回答by himanshupareek66
It might not relate as answer to this question, but some important aspects on primary key and Clustered Indexes are ->
它可能与这个问题的答案无关，但主键和聚集索引的一些重要方面是 ->
If there is a primary key (By Default Which is Clustered Index, however we can change that) with Clustered Index, then we can not create one more clustered index for that table. But if there is not a primary key set yet, and there is a clustered index, then we can't create a primary key with Clustered Index.
如果有一个带有聚集索引的主键（默认情况下它是聚集索引，但是我们可以改变它），那么我们不能为该表创建更多的聚集索引。但是如果还没有设置主键，并且有聚集索引，那么我们就不能用聚集索引创建主键。
