(Original) Analysis of SQL Server indexes (I)

I. Concepts
An index is a distributed storage structure created to accelerate the retrieval of data rows in a table.
1. indexes are created for tables;
2. An Index consists of an index page other than the data page for storing tables. That is to say, an index requires a separate storage space.

Ii. Index classification
There are two types of indexes: clustered index and non-clustered index.
Shows the structure of the clustered index:

 
"Root Node" and "Intermediate Level" are collectively referred to as "index pages", also known as "non-leaf level ". The root node stores the first element of each page at the intermediate level. Store the first element of each page of the data page at the intermediate level.
Shows the structure of a non-clustered index:

The root node stores the first element of each page at the intermediate level. However, storing the data page in the middle level is not the first element of each page, but maintains a hash table-like structure in the middle level.
Comparison of the two indexes:
1. In clustered indexes, table data is arranged in the order of indexes. In non-clustered indexes, the physical order of the table is different from that of the index, that is to say, the table data is not sorted by the index column.
2. Each table can have only one clustered index, which should be the first index. The non-clustered index of each table can reach 294.
3. The middle-level elements of these two indexes are arranged sequentially.

Iii. How to accelerate the query of Indexes
From the concept, we know that one feature of an index is to accelerate the retrieval of data rows in a table. How is it accelerated?
The index is similar to the book directory or appendix. we can locate the book content directly by looking for the page number in the book directory. The index is a direct path to the data. (Note: the SQL Server Query Optimizer depends on indexes)