How does the PRIMARY KEY keyword relate to clustered indexes in SQL Server?
PRIMARY KEY关键字如何与SQL Server中的聚簇索引相关?
(Some people seem to want to answer this question instead of a different question I asked, so I am giving them a better place to do so.)
(有些人似乎想回答这个问题,而不是我问的另一个问题,所以我给他们一个更好的地方去做。)
3 个解决方案
#1
5
The clustered index of a table is normally defined on the primary key columns.
表的聚簇索引通常在主键列上定义。
This, however is not a strict requirement.
然而,这不是严格的要求。
From MSDN:
When you create a PRIMARY KEY constraint, a unique clustered index on the column or columns is automatically created if a clustered index on the table does not already exist and you do not specify a unique nonclustered index.
创建PRIMARY KEY约束时,如果表上的聚簇索引尚不存在且您未指定唯一的非聚簇索引,则会自动创建一个或多个列上的唯一聚簇索引。
And:
You can create a clustered index on a column other than primary key column if a nonclustered primary key constraint was specified.
如果指定了非聚簇主键约束,则可以在主键列以外的列上创建聚簇索引。
#2
9
How does the
PRIMARY KEYkeyword related to clustered indexes in MS SqlServer?PRIMARY KEY关键字如何与MS SqlServer中的聚簇索引相关?
By default, a PRIMARY KEY is implemented as a clustered index. However, you can back it by an unclustered index as well (specifying NONCLUSTERED options to its declaration)
默认情况下,PRIMARY KEY实现为聚簇索引。但是,您也可以通过非聚簇索引对其进行备份(为其声明指定NONCLUSTERED选项)
A clustered index is not necessarily a PRIMARY KEY. It can even be non-unique (in this case, a hidden column called uniqueifier is added to each key).
聚簇索引不一定是PRIMARY KEY。它甚至可以是非唯一的(在这种情况下,每个键都会添加一个名为uniqueifier的隐藏列)。
Note that a clustered index is not really an index (i. e. a projection of a table ordered differently, with the references to original records). It is the table itself, with the original records ordered.
注意,聚集索引实际上不是索引(即,以不同方式排序的表的投影,具有对原始记录的引用)。这是表本身,订购了原始记录。
When you create a clustered index, you don't really "create" anything that you can drop apart from the table. You just rearrange the table itself and change the way the records are stored.
创建聚簇索引时,您实际上并没有“创建”可以从表中删除的任何内容。您只需重新排列表本身并更改记录的存储方式。
#3
4
A primary key is, as the name implies, the primary unique identifier for a row in your table. A clustered index physically orders the data according to the index. Although SQL Server will cluster a primary key by default, there is no direct relationship between the two.
顾名思义,主键是表中行的主要唯一标识符。聚簇索引根据索引对数据进行物理排序。虽然SQL Server默认会集群主键,但两者之间没有直接关系。
#1
5
The clustered index of a table is normally defined on the primary key columns.
表的聚簇索引通常在主键列上定义。
This, however is not a strict requirement.
然而,这不是严格的要求。
From MSDN:
When you create a PRIMARY KEY constraint, a unique clustered index on the column or columns is automatically created if a clustered index on the table does not already exist and you do not specify a unique nonclustered index.
创建PRIMARY KEY约束时,如果表上的聚簇索引尚不存在且您未指定唯一的非聚簇索引,则会自动创建一个或多个列上的唯一聚簇索引。
And:
You can create a clustered index on a column other than primary key column if a nonclustered primary key constraint was specified.
如果指定了非聚簇主键约束,则可以在主键列以外的列上创建聚簇索引。
#2
9
How does the
PRIMARY KEYkeyword related to clustered indexes in MS SqlServer?PRIMARY KEY关键字如何与MS SqlServer中的聚簇索引相关?
By default, a PRIMARY KEY is implemented as a clustered index. However, you can back it by an unclustered index as well (specifying NONCLUSTERED options to its declaration)
默认情况下,PRIMARY KEY实现为聚簇索引。但是,您也可以通过非聚簇索引对其进行备份(为其声明指定NONCLUSTERED选项)
A clustered index is not necessarily a PRIMARY KEY. It can even be non-unique (in this case, a hidden column called uniqueifier is added to each key).
聚簇索引不一定是PRIMARY KEY。它甚至可以是非唯一的(在这种情况下,每个键都会添加一个名为uniqueifier的隐藏列)。
Note that a clustered index is not really an index (i. e. a projection of a table ordered differently, with the references to original records). It is the table itself, with the original records ordered.
注意,聚集索引实际上不是索引(即,以不同方式排序的表的投影,具有对原始记录的引用)。这是表本身,订购了原始记录。
When you create a clustered index, you don't really "create" anything that you can drop apart from the table. You just rearrange the table itself and change the way the records are stored.
创建聚簇索引时,您实际上并没有“创建”可以从表中删除的任何内容。您只需重新排列表本身并更改记录的存储方式。
#3
4
A primary key is, as the name implies, the primary unique identifier for a row in your table. A clustered index physically orders the data according to the index. Although SQL Server will cluster a primary key by default, there is no direct relationship between the two.
顾名思义,主键是表中行的主要唯一标识符。聚簇索引根据索引对数据进行物理排序。虽然SQL Server默认会集群主键,但两者之间没有直接关系。