MS SQL Basics Tutorial: Database indexing

The most frequent operation of a user's database is to query the data. In general, a database needs to search the entire table for a query operation. When the data in the table is very large, it takes a long time to search the data, which causes the server to waste resources. In order to improve the ability of retrieving data, the indexing mechanism is introduced into the database. This chapter describes the concept of indexing and its creation and management.

The concept of 8.1.1 index

An index is a separate, physical database structure that is the collection of one or more column values in a table and the corresponding list of logical pointers to the data pages that physically identify those values in the table. The index is based on the table, which provides an internal method of arranging the data in the table in the database. The storage of a table consists of two parts, one for the data page of the table and the other for the index page. The index is placed on the index page, which is usually much smaller than the data page. When data retrieval is done, the system searches the index page, finds the pointer to the desired data, and then reads the data directly from the data page through the pointer. To some extent, the database can be regarded as a book, the index as a book directory, through the directory to find information in the book, obviously less than the directory of books convenient and fast.

There is a greater correlation between indexes and keywords and constraints. Keywords can be grouped into two categories: one is a logical keyword, which is described in the Database Base Chapter section, and the other is a physical keyword that defines the index's column, or index.

Structure of the 8.1.2 index

(1) The B-tree structure of the index

The indexes in SQL Server are maintained in a B-tree structure, as shown in Figure 8-1. B-Tree is a multi-level, self maintenance structure. A B-tree consists of a top-level, called the root node, 0 to multiple middle layers (intermediate), a low-level (level 0), and a number of leaf nodes (leaf node) in the underlying layer. In Figure 8-1, each box represents an index page, the larger the width of the index column, the deeper the B-tree is, that is, the more layers,

The more index pages to be accessed by reading records. That is, the performance of the data query will decrease as the number of index column levels increases. Fig. 8-1 The B-tree structure of the index

In SQL Server databases, the indexes are grouped into two categories: Cluster index (Clustered index) and nonclustered index (nonclustered index).

(2) The cluster index cluster index sorts the data in the physical data page of the table by column and then back to disk, where the cluster index is mixed with the data, and the actual data is stored in its leaf nodes. Because the cluster index sorts the data in a table, it is very quick to find data with a clustered index. But because the cluster index completely rearranges all the data in the table, it requires a particularly large amount of space, roughly 120% of the space occupied by the table's data. A table's data rows can only be stored on disk in one sort way, so a table can have only one cluster index.

(3) Non-clustered index nonclustered indexes have a completely separate structure from the data in the table, and using a nonclustered index does not need to sort the data in a physical data page by column. The values and row locators of the keywords that make up the nonclustered index are stored in the leaf nodes of the nonclustered index. The structure and storage content of a row locator depends on how the data is stored. If the data is stored in a clustered index, the index key of the cluster is stored in the row locator, and if the data is not stored in a clustered index, which is also known as heap storage (Heap Structure), the row locator stores a pointer to the data row. A nonclustered index sorts a row locator in a certain way by the value of the keyword, which does not match the order of the table's rows in the data page. Because a non-clustered index uses index page storage, it requires more storage space than a clustered index and is less efficient but a table can only have one clustered index, and a nonclustered index is required when a user needs to build multiple indexes. Theoretically, a table can build up to 249 nonclustered indexes.

See the full set of "MS SQL Basics Tutorials"