SQL Server
来源:互联网 发布:算法谜题 百度云 编辑:程序博客网 时间:2024/06/02 07:05
In SQL Server, indexes are organized as B-trees. Each page in an index B-tree is called an index node. The top node of the B-tree is called the root node. The bottom level of nodes in the index is called the leaf nodes. Any index levels between the root and the leaf nodes are collectively known as intermediate levels. In a clustered index, the leaf nodes contain the data pages of the underlying table. The root and intermediate level nodes contain index pages holding index rows. Each index row contains a key value and a pointer to either an intermediate level page in the B-tree, or a data row in the leaf level of the index. The pages in each level of the index are linked in a doubly-linked list.
Clustered indexes have one row in sys.partitions, with index_id = 1 for each partition used by the index. By default, a clustered index has a single partition. When a clustered index has multiple partitions, each partition has a B-tree structure that contains the data for that specific partition. For example, if a clustered index has four partitions, there are four B-tree structures; one in each partition.
Depending on the data types in the clustered index, each clustered index structure will have one or more allocation units in which to store and manage the data for a specific partition. At a minimum, each clustered index will have one IN_ROW_DATA allocation unit per partition. The clustered index will also have one LOB_DATA allocation unit per partition if it contains large object (LOB) columns. It will also have one ROW_OVERFLOW_DATA allocation unit per partition if it contains variable length columns that exceed the 8,060 byte row size limit. For more information about allocation units, see Table and Index Organization.
The pages in the data chain and the rows in them are ordered on the value of the clustered index key. All inserts are made at the point where the key value in the inserted row fits in the ordering sequence among existing rows. The page collections for the B-tree are anchored by page pointers in the sys.system_internals_allocation_units system view.
Important:
The sys.system_internals_allocation_units system view is reserved for Microsoft SQL Server internal use only. Future compatibility is not guaranteed.
For a clustered index, the root_page column in sys.system_internals_allocation_units points to the top of the clustered index for a specific partition. SQL Server moves down the index to find the row corresponding to a clustered index key. To find a range of keys, SQL Server moves through the index to find the starting key value in the range and then scans through the data pages using the previous or next pointers. To find the first page in the chain of data pages, SQL Server follows the leftmost pointers from the root node of the index.
This illustration shows the structure of a clustered index in a single partition.
https://technet.microsoft.com/en-us/library/ms177443(v=sql.105).aspx
- sql server
- SQL SERVER
- sql server
- SQL Server
- SQL Server
- SQL SERVER
- SQL Server
- sql server
- sql server
- SQL SERVER
- SQL Server
- SQL SERVER
- SQL Server
- SQL SERVER
- sql server
- sql server
- sql server
- sql server
- Linux查看CPU和内存使用情况
- WebView使用解析(二)之WebViewClient/WebChromeClient
- Kafka性能测试分析
- 两张二维人物图像互相换脸代码
- Kotlin开发之旅《二》—Kotlin的基本语法
- SQL Server
- BZOJ 2466: [中山市选2009]树 高斯消元
- PHP中的魔术常量、预定义常量和预定义变量
- Python3处理HTTP请求
- 常用排序算法小结
- Excel VBA高效办公应用-第七章-VBA财务报表分析-Part1 (损益表的分析)
- Mybaities一对多注解和xml的实现
- kotlin学习5(字符,字符串,元组)
- 解决Spring MVC 参数乱码问题