In-depth understanding of SQL Server's non-clustered Index Structure

We know that SQL Server's data Row Storage has two data structures: A: heap B: B (binary Tree)

Data is sorted and stored according to one of these two types. Friends who have learned the data structure should know the binary tree. Why is binary tree used? Because binary search is convenient and fast

Find the data. If it is a heap, the data is not sorted in any order, and there is no structure, and the data page is not connected at the beginning and end, unlike the B tree, the data page

Use a two-way linked list to connect to the beginning and end. Heap tables only rely on the IAM page (index allocation ing page) in the table to link the heap page. IAM records the page number and location.

Unless there is a clustered index in the table, if not, the storage of the data in the table is a heap structure.

What about non-clustered indexes? Is non-clustered index also a heap structure? In fact, SQLSERVER has several page types (data is stored on one page, just as Windows Memory also uses pages for Organization)

One is the index page and the other is the data page.

I feel that many books are hard to tell, just like me. At the beginning of the article, I also said:Data Row Storage has two data structures: A: heap B: B (Binary Tree)

I think there should be two data structures in the storage of data pages: 1. Heap 2 and B.

First, the index pages, whether clustered or non-clustered indexes, are stored on the index pages, and the index pages are stored in the B-tree structure.

Heap page: the actual data row. If no clustered index exists in the table, the actual data is placed on the heap page. If the clustered index exists, the data is placed on the index page.

The node in Tree B is actually called a page, also called a node. In Tree B, there will be a page: root page (that is, the root node ), both non-clustered indexes and clustered indexes are the same.

Therefore:Data Row Storage has two data structures: A: heap B: B (Binary Tree)

Should I understand the above sentence?

The data pages in the heap are stored randomly, and the only logical connection between data pages is recorded on the IAM page, therefore, the IAM page also plays the role of root page.

What about Clustered indexes? Is the clustered index also a heap structure?

The non-clustered index is placed on the index page, the B-tree structure, and the data is still placed on the heap page. If the data page is placed in the heap, it is not like the clustered index in the leaf node of the B-tree.

(That is, on the index page), how does a non-clustered index relate to the actual data?

The answer is: there will be a row positioner on a leaf node (leaf page) with non-clustered indexes, and the role of the row positioner can be seen in the red text below

Non-clustered indexes and clustered indexes share the same B-tree structure, and the significant difference between them lies in the following two points:

(1) data rows in the base table are not sorted and stored in the order of non-clustered keys.

(2) The leaf layer of a non-clustered index is composed of index pages rather than data pages.

(3) A non-clustered index table can be a B-tree or a heap.

(4) If the table is a heap (meaning that the table has no clustered index), the row locator points to the row pointer. This pointer is generated by the File Identifier ID: page number: number of lines on the page. Whole

A pointer is called a row ID (RID)

(5) If a table has a clustered index or a clustered index in the index view, the row locator points to the clustered index key. By using

Pointer (pointing to clustered index key) Search for clustered index to retrieve actual data

Non-clustered indexes do not change or improve the storage mode of actual data pages. Its B-tree structure is only for its ownIndex page.

However, non-clustered indexes play the same role as clustered indexes, which is equivalent to checking the word table.

Let's end with a summary. What is this short summary?

Of course, it is to summarize the structure of the table, because someone once asked about the structure of a table in the Forum.

A. Tables with clustered indexes, clustered indexes, and non-clustered indexes (B tree)

B does not have any index (HEAP)

C. No clustered index and non-clustered index (B-tree + heap)

Someone in the Forum abbreviated asClustering table, B and C areHeap table

I remember some of the data structures that I previously talked about in the University: big data, small data, two-way linked list, and binary tree.

Unfortunately, all the teachers are returned now.

Attachment

Heap table structure

Non-clustered Index Structure

Clustered Index Structure