The limit of my understanding how a database stores a primary key is a btree based on the clustered key with each node storing the rest of the columns.
我理解数据库如何存储主键的限制是基于聚簇键的btree,每个节点存储其余列。
I have not updated this understanding since university last century and would love to know how SQL Server stores a composite primary key compared to a single primary key.
自上个世纪大学以来,我没有更新这种理解,并且很想知道SQL Server如何存储复合主键与单个主键相比。
Can anyone help point me to some detail on this?
任何人都可以帮我指点一些细节吗?
1 个解决方案
#1
4
A composite key is exactly the same as a ordinary, single column, key, but is longer and composed of multiple values. Consider you have a B-Tree on a single column, say A. The non-leaf pages contains slots with values of A column, and pointers to leaf-pages. The leaf pages contain slots with the column A values (the key), followed by all the other columns of the row. A composite key is exactly the same, but the values in the slots will be the composite values, in the order they are declared in the key.
复合键与普通的单列键完全相同,但更长并且由多个值组成。假设您在单个列上有一个B树,比如说A.非叶子页面包含值为A列的插槽,以及指向叶子页面的指针。叶子页面包含具有列A值(键)的插槽,后面是行的所有其他列。复合键完全相同,但插槽中的值将是复合值,按照它们在键中声明的顺序。
There is a great description of the internals of a SQL Server data page at Anatomy of a Page. Also, the Chapter 6 from Kelan Delaney's old SQL 7.0 book is available online on Technet: Tables. Is still a great resource, the basics still apply to SQL 2008 R2 (things that are changed are mostly around max types and compression settings, but the bulk of info is still valid).
在Anatomy of a页面上有一个很好的SQL Server数据页面内部描述。另外,Kelan Delaney的旧SQL 7.0书中的第6章可以在Technet上找到:Tables。仍然是一个很好的资源,基础知识仍然适用于SQL 2008 R2(更改的内容主要是围绕最大类型和压缩设置,但大部分信息仍然有效)。
#1
4
A composite key is exactly the same as a ordinary, single column, key, but is longer and composed of multiple values. Consider you have a B-Tree on a single column, say A. The non-leaf pages contains slots with values of A column, and pointers to leaf-pages. The leaf pages contain slots with the column A values (the key), followed by all the other columns of the row. A composite key is exactly the same, but the values in the slots will be the composite values, in the order they are declared in the key.
复合键与普通的单列键完全相同,但更长并且由多个值组成。假设您在单个列上有一个B树,比如说A.非叶子页面包含值为A列的插槽,以及指向叶子页面的指针。叶子页面包含具有列A值(键)的插槽,后面是行的所有其他列。复合键完全相同,但插槽中的值将是复合值,按照它们在键中声明的顺序。
There is a great description of the internals of a SQL Server data page at Anatomy of a Page. Also, the Chapter 6 from Kelan Delaney's old SQL 7.0 book is available online on Technet: Tables. Is still a great resource, the basics still apply to SQL 2008 R2 (things that are changed are mostly around max types and compression settings, but the bulk of info is still valid).
在Anatomy of a页面上有一个很好的SQL Server数据页面内部描述。另外,Kelan Delaney的旧SQL 7.0书中的第6章可以在Technet上找到:Tables。仍然是一个很好的资源,基础知识仍然适用于SQL 2008 R2(更改的内容主要是围绕最大类型和压缩设置,但大部分信息仍然有效)。