SQL Server Performance Optimization (9) Clustered index

I. Concept and classification of indexes

The concept of indexing everyone knows that we also use common clustered indexes, nonclustered indexes in our daily development. However, in addition to these two, SQL Server also provides other indexes, such as:

A. Unique index: An index that does not contain duplicate keys, and either a clustered index or a nonclustered index can be a unique index.

B. Index containing columns: it expands to include not only key columns but also nonkey columns.

C. Full-text indexing

D. Spatial index

E. Filtering indexes

F. XML

Of course, the above-mentioned categories are basically useless except for a and B.

II. structure of the clustered index

To use a clustered index, you must understand the concept of a clustered index and how it works. Online to the aggregate index description of the data there are many, the most popular explanation is to take the dictionary example: if we use pinyin in the Xinhua dictionary to check a Chinese character "rope", is the first few pages from the dictionary of the letter "S" began, and then check the "suo", and then find the word "rope", according to its page, Turn to the page where the word is located.

Of course this requires that

1. All Chinese characters in the Xinhua dictionary are arranged in phonetic alphabet order.

2. The Xinhua dictionary has a phonetic lookup appendix in front of it.

Clustered index is also the reason,

1. There is a "phonetic" list of all the data, that is, the index.

2. All data is arranged sequentially on the hard disk according to this index, which is the B-tree.

B-Tree concept: http://www.baidu.com/link?url=5cmF2QROgNE6JUpvbu_ 9oqwyhdtgvn-1wscae98nb6ek70h6tc4s3s3skgfvalboiwheubljiai6moivawk5jq&wd=&eqid= Ae27fb0e0003091b00000004555ae6b3&ie=utf-8

Structure of the clustered index

SQL Server Performance Optimization (9) Clustered index