SQL Server's nonclustered index structure depth understanding _mssql

We know that SQL Server data row storage has two kinds of data structure: A: Heap b:b tree (binary two fork tree)

Data is sorted and stored in one of two ways, and friends who have studied data structures should know how to use binary trees, because it is convenient to use the binary search method to quickly

Find the data. If it is a heap, then the data is not sorted in any order, there is no structure, the data page is not end-to-end, unlike the B-tree, the data page

Use a two-way linked list to connect to the end. The heap table relies only on the IAM page (index allocation map page) of the tables to link the heap pages together, the IAM records the page number, the page position

Unless the table has a clustered index, if not, the data stored in the table is the heap structure.

What about nonclustered indexes? Nonclustered indexes are also heap structures? In fact, SQL Server has several page types (data is stored on one page, just as Windows memory is organized using pages)

One is the index page, one is the data page

I feel like a lot of books, as I did at the beginning of the article: there are two kinds of data structures in the storage of a row of rows: A: Heap b:b tree (binary tree)

I think it's supposed to be. Data page storage has two kinds of structure: 1, heap 2, B-Tree

First of all: index pages, whether clustered or nonclustered, are stored on index pages, and index pages are stored in the structure of B-trees.

The heap page: that is, the actual data row, if the table does not have a clustered index, then the actual data is placed on the heap page, if the clustered index, then the data is placed on the index page

The B-tree node, in fact, called the page, also called node in the B-tree will have a page: Root page (that is, the root node), nonclustered index and clustered index are the same

So: data row Storage has two kinds of data structure: A: Heap b:b tree (two fork tree)

Do you understand the above statement?

In a word, the data pages in the heap are completely random, and the only logical connection between the data pages is recorded in the IAM page, so the IAM page plays the role of root page.

So what about the clustered index? The clustered index is also the heap structure?

Nonclustered indexes are placed on index pages, B-tree structure, data is still on the heap page, so if the data page is placed in the heap, it is not like a clustered index in the leaf node of the B-tree

(That is, in the index page), how does the nonclustered index and the actual data relate?

The answer is: there will be a row locator in the leaf node (leaf page) of the nonclustered index, and the function of the row locator look at the red word below.

Nonclustered indexes have the same B-tree structure as clustered indexes, and the significant difference between them is the following two points:

(1) The data rows of the underlying table are not sorted and stored in the order of the nonclustered keys

(2) The leaf layer of a nonclustered index is made up of index pages rather than data pages

(3) A table that establishes a nonclustered index can be a B-tree, or it can be a heap

(4) If the table is a heap (meaning that the table does not have a clustered index), the row locator points to a pointer to the row. The pointer is generated by the file Identifier ID: page number: line count on the page. Whole

The pointer is called a row ID (RID)

(5) If the table has a clustered index or has a clustered index on the indexed view, the row locator points to the clustered index key. SQL is stored in a nonclustered index by using the

Pointer (point to the clustered index key) to search for a clustered index to retrieve the actual data

Nonclustered indexes do not change or improve the storage mode of the actual data pages. His B-tree structure is only for its own index page face.

However, a nonclustered index is the same as a clustered index, which is equivalent to the CJK ideographs table

In conclusion, what is the summary of this short essay?

Of course is the structure of the summary table, because once someone in the forum asked the structure of a table

A table with a clustered index or a clustered index or nonclustered index (b-Tree)

B no Index (heap)

C no clustered index, nonclustered index (b-Tree + heap)

Someone in the forum referred to as a clustered table , B and C for the heap table

Remember before in the university data structure teacher talked about a number of structures: Dagen, small Gan, two-way linked list, two-fork tree

But now it's all back to the teacher.

Attached pictures

The structure of the heap table

Structure of nonclustered indexes

Structure of the clustered index