SQL Server 2005：主键列和表列 - 它们的顺序必须相同吗？
概念检查：
根据我的理解，主键（本质上是聚集索引）中的列顺序应该根据不同程度来决定 参见此处）
但是我没有看到任何明确提及 实际表和主键。
或者换句话说，如果PK中列的最佳顺序是(B, C, A)，那么如果表中的列顺序为A, B，会对性能产生影响吗？ C（即与PK定义不同）？
create table ABC (
A int,
B varchar(10),
C int)
我使用 SQL Server 2005 的经验似乎表明这并不重要
A concept check:
from what I understand, the order of columns in a primary key (which is essentially a clustered index) should be decided based on the level of distinctness See here)
But I did not see any explicit mention of the correlation between the order of columns in the actual table and the primary key.
Or put it in the other way, if the optimal order of columns in PK is (B, C, A), will there be any performance impact if the table has the columns ordered as A, B, C (i.e. different from the PK definition)?
create table ABC (
A int,
B varchar(10),
C int)
My experience with SQL Server 2005 seems to indicate it does not matter
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
表中列的“顺序”是表定义方式的产物 - 关系数据库的关键概念之一是列按名称定位，而不是按顺序位置定位。
数据库中列的实际布局可能与您给出的顺序不匹配 - 例如，如果您在表上定义了多个位列，则每组八个列将被打包到一个字节中，无论它们位于何处出现在表定义中。
再例如，所有固定长度的列都打包在行的开头。因此，在您的示例
ABC表中，磁盘上每行的列顺序实际上是A、C、B< /code> （但在列A之前以及C和B之间也出现了一些附加结构。简而言之，不，顺序表中的列不应产生任何影响。
您还可以找到大量的 关于 SO 的问题示例人们询问如何在表中的特定位置插入列，并给出类似的答案 - 列出现在表定义中的位置并不重要 - 重要的是列出现在选择列表中的位置，并且，您已经提到，在索引定义中，
您也不应该将主键和聚集索引混为一谈 - 两者不必捆绑在一起，这只是主键将成为表上的聚集索引（如果存在）的默认行为。尚未定义聚集索引。
The "order" of columns in a table are an artifact of the way tables are defined - one of the key concepts of relational databases is that columns are located by name, not by ordinal position.
The actual layout of the columns in the database may not match the order that you've given - for instance, if you have multiple bit columns defined on the table, each set of eight will be packed into a single byte, no matter where they appear in the table definition.
For another example, all fixed length columns are packed at the start of the row. So in your example
ABCtable, the order of the columns in each row on disk would actually beA,C,B(but with some additional structures also appearing before columnA, and betweenCandB.In short, no, the order of columns in the table should not have any impact.
You can also find plenty of examples of questions on SO where people are asking for ways to insert a column at a particular position within the table, and given similar answers - it should not matter where the column appears in the table definition - all that matters is where the column appears in select lists and, as you've mentioned, within index definitions.
You should also not conflate primary key and clustered index - the two do not have to be tied together. It's just the default behaviour that the primary key will become the clustered index on the table, if there isn't already a clustered index defined.
主键是一组属性。原则上，键的属性没有顺序。不幸的是，SQL Server 使用键创建语法中指定列名的顺序来确定支持该键的索引中列的顺序。因此，您必须以与考虑索引中列的顺序相同的方式来考虑指定的顺序。
主键与聚集索引不同。 SQL Server 需要索引来支持键约束，但您可以指定索引是聚集索引还是非聚集索引。
A primary key is a set of attributes. In principle there is no order to the attributes of a key. Unfortunately SQL Server uses the order in which column names are specified in the key creation syntax to determine the order of columns in the index that supports that key. So you have to think about the specified ordering in just the same way as you consider the order of columns in an index.
A primary key is not the same thing as a clustered index. SQL Server requires an index to support key constraints but you can specify that the index is either clustered or nonclustered.
没关系，没有意义（不同顺序）
It does not matter and makes no sense (different order)