Detailed SQL Server database index

I. Understanding the structure of an index

The role of an index in a database is similar to the role of a directory in a book to improve the speed of finding information. Use the index to find the data, without having to scan the entire table to quickly find the data you need. Microsoft's SQL Server provides two indexes: a clustered index (clustered index, also known as a clustered, clustered index) and a nonclustered index (nonclustered index, also known as a nonclustered and nonclustered index).

The basic unit of data storage in SQL Server is a page. The disk space allocated by the data files (. mdf or. ndf) in the database can be logically divided into pages (numbered consecutively from 0 to N). Disk I/O operations are performed at the page level. That is, the smallest unit of data that SQL Server reads or writes to data is a data page.

Here's a quick look at the architecture of the index:

1. Clustered index Structure

In SQL Server, indexes are organized in a B-tree structure.

Clustered index The structure in a single partition:

--Establish useradddate clustered index

CREATE CLUSTERED INDEX [IX_UserAddDate] ON [ASPNet_zSurvey].[ZS_User]
(
[UserAddDate] ASC
)

Clustered index (Clustered index) features

The leaf node of the clustered index is the actual data page

The sort order in a clustered index simply indicates that the data page chain is logically ordered. Rather than sequentially physically stored on disk

The physical position of the row and the position of the row in the index are the same

Only one clustered index per table

The average size of the clustered index is about 5% of the size of the table

2. Nonclustered index structure

Nonclustered indexes have the same B-tree structure as clustered indexes, and the significant difference between them is the following two points:

1. The data rows of the underlying table are not sorted and stored in the order of the nonclustered keys.

2. The leaf layer of a nonclustered index is made up of index pages rather than data pages.

The following figure illustrates the nonclustered index structure in a single partition: