Shulou(Shulou.com)05/31 Report--

What is the principle of nonclustered index in SQLSERVER? aiming at this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

We know that there are two data structures for storing SQLSERVER data rows: a: heap B: B tree (binary binary tree)

Data is sorted and stored according to one of these two kinds. Friends who have studied data structure should know why binary tree is used, because it is convenient to use binary search method to speed up.

Find the data. If it is a heap, then the data is not sorted in any order, there is no structure, and the data pages are not connected from end to end, unlike B-trees and data pages.

Use a two-way linked list to connect end to end. The heap table only relies on the IAM page (index allocation mapping page) in the table to link the heap pages together. The page number and page location are recorded in the IAM.

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

What about nonclustered indexes? Is a nonclustered index also a heap structure? In fact, SQLSERVER has several page types (data is stored on a page-by-page basis, just as Windows memory is organized using pages)

One of them is the index page, the other is the data page.

I feel that many books are unclear, just like me, I also said at the beginning of the article: the storage of data rows has two data structures: a: heap B: B tree (binary tree)

I think there are two kinds of data structures in the storage of data pages: 1, heap 2, B-tree

Let's start with: index pages, whether clustered or nonclustered, the index data is stored in the index page, which is stored in the B-tree structure.

And heap page: that is, the actual data row, if there is no clustered index in the table, then the actual data is placed on the heap page, if the clustered index, then the data is placed on the index page

And the node in the B-tree, which is actually called a page, or a node, will have a page in the B-tree: root page (that is, the root node), nonclustered index and clustered index are the same.

So: there are two kinds of data structures for storing data rows: a: heap B: B tree (binary tree)

Do you understand the above sentence?

Again, the data pages in the heap are stored at 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.

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

The nonclustered index is placed on the index page, the B-tree structure, and the data is still placed on the heap page, so if the data page is placed in the heap, unlike the clustered index in the leaf node of the B-tree.

(that is, in the index page), how does the nonclustered index relate to the actual data?

The answer is: there is a row locator in the leaf node (leaf page) of a nonclustered index, and for the role of the row locator, see the red word below

A nonclustered index has the same B-tree structure as a clustered index, and there are two significant differences between them:

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

(2) the leaf layer of a nonclustered index consists of index pages rather than data pages.

(3) the table that builds a nonclustered index can be a B-tree or a heap.

(4) if the table is a heap (meaning that the table does not have a clustered index), the row locator points to the pointer to the row. The pointer is generated by the file identifier ID: page number: the number of lines on the page. The whole

The pointer is called a row ID (RID)

(5) if the table has a clustered index or the indexed view has a clustered index, the row locator points to the clustered index key. SQL by using the

Pointer (to the clustered index key) searches the clustered index to retrieve the actual data

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

However, a nonclustered index has the same function as a clustered index, which is equivalent to a word table.

Finally, let's sum up. What is the summary of such a short essay?

The structure of the summary table, of course, because someone once asked about the structure of a table in the forum.

A table with a clustered index or a clustered index or a nonclustered index (B-tree)

B does not have any indexes (heap)

C has no clustered index, but nonclustered index (B-tree + heap)

The answer to the question about the principle of nonclustered index in SQLSERVER is shared here. I hope the above content can be of some help to everyone. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.