[SQL Server] Index

Let's take a simple look at how the SQL Server Index works. We will not talk about the index concepts.

Clustered index:

(Figure)

Let's 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. You can see that it is stored on one page and one page. Assume that the column is "lastname ".

Because it is a clustered index, it is arranged in order. We can see that the index is a tree. First, let's take a look at how the tree is formed.

First, let's look at the top of page100 and page110, which form page141. the first data of page141 is the first data of page100, And the last data of page141 is the first data of page110. For the same reason, page120 and page130 form page145, page141, and page145 form the root page140.

Well, let's see how it looks for data. Let's look for the surname "Rudd.

First, it will start from the root, namely page140, because the value of "Rudd" is larger than that of "Martin" (you only need to compare their first letter, in 26 alphabetic order, R is placed behind m). So we will search for "Martin", that is, find page145, and then compare "Rudd" and "Smith ", "Rudd" is smaller than "Smith", so page120 will be searched on the left side, and then scanned in page120 line by line until "Rudd" is found ".

If no index is created, SQL Server will scan each page sequentially from the first page until "Rudd" is found ". Obviously, for a million-row table, the efficiency is extremely low. If an index is created, it can be found very quickly.

Non-clustered index:

(Figure B) 

See Figure B. Non-clustered index structure.

The difference between a clustered index and a non-clustered index is that the physical storage sequence of the clustered index data is consistent with the index sequence, that is, the data is sorted in order. Data storage of Non-clustered indexes is unordered and not arranged in the order of indexes.

Figure B shows that the data page is unordered. How is its index created?

See Figure B. It copies the data in the index column and then sorts the data in order to create an index. Each row of data has a pointer.

Let's look for "Rudd" again. First, start from the root of the index page. The search principle is the same as that of clustered indexes. Find "Rudd" in page61 on the index page, and its pointer is 470501. Then, find 470501 in page5 on the data page, which is the actual location of "Rudd" in the database, in this way, we found "Rudd ".

Well, the basic working principle of indexes is as follows, which may be more complicated.