How the "Go" SQL Server index Works

Because of the invisible nature of the index, many people may also be confused about how it works, so they often don't know how to start indexing.
Some basic concepts:

1. Data page: see Indexing principle. In a clustered index, it is the leaf node of the index page.
Let's take a quick look at how it works.

Figure A
Let's take a look at figure A, the structure of the clustered index.
The data page is the place where the data is actually stored in the database, and you can see that it is saved on page 1 Page 1 page.

Suppose that column is "LastName".

Because it is a clustered index, it is sorted in order. As you can see, the index is a tree, first look at how the tree formed.

First look at the top of the Page100 and Page110, formed by them page141,page141 the first data is the first Data Page100, Page141 the last data is the first Data Page110. In the same vein, Page120 and Page130 form page145,page141 and Page145 form the root Page140.

Well, then look at how it looks for data. (Find ideas similar to two-point lookup)

We're looking for the name "Rudd."

First it will start from the root, that is, Page140, because the value of "Rudd" is larger than "Martin" (as long as they compare their initials to know, in 26 alphabetical order R in the back of M), so will go to "Martin" behind the search, that is, find Page145, and then in comparison " Rudd "and" Smith "," Rudd "is smaller than" Smith ", so it will be Page120 to the left, and then scan down the Page120 line until" Rudd "is found.

If you do not build an index, SQL Server scans the past each page sequentially, starting from the first page, until "Rudd" is found. Obviously if the efficiency is extremely low for a million-row table, if the index is built, it can be found very quickly.

Non-clustered index:

Figure B
Look at Figure B, the structure of the non-clustered index.

The difference between clustered and nonclustered indexes is that the data of the clustered index is physically stored in the same order as the index order, i.e. its data is sorted out sequentially. Non-clustered index data stores are unordered and are not indexed in order.

From figure B You can see that the data page is unordered. So how is its index built?

Looking at figure B, it duplicates the data of this index column and then sequentially lists it and then builds the index. Each row of data has a pointer.

Let's look for "Rudd". First, look at the root of the index page, and the lookup principle is the same as the clustered index. In the index page of the Page61 found "Rudd", its pointer is 470501, and then in the data page Page5 found 470501, this position is "Rudd" in the database in the actual location, so that the "Rudd" found.

Clustered and nonclustered indexes have a B-tree structure, but there are two significant differences:

1. The leaf layer of a nonclustered index does not contain a data page. Instead, leaf nodes contain index rows.

2. Data rows are not sorted and stored in the order of the nonclustered index keys.

Okay, so that's how the index works, and it might actually be more complicated.

Additional: Nonclustered index Search