1. Primary key index: Rows with the same index value are not allowed, so duplicate indexes or key values are prohibited. The system checks whether there are duplicate key values when creating an index, and checks every time data is added by using an INSERT or UPDATE statement.

2. Clustering index: refers to that the physical order of data in database table rows is the same as the logical (index) order of key values. A table can only have one clustered index, because a table has only one physical order.

3. Non-clustered index: The logical order of indexes in an index is different from the physical storage order on disk. The leaf level of a nonclustered index does not contain data pages. Instead, leaf nodes contain index rows.

Extended data

A clustered index is especially effective for columns that frequently search for range values. After using a clustered index to find the row containing the first value, you can ensure that the rows containing subsequent index values are physically adjacent.

For example, if the query executed by the application often retrieves records within a certain date range, you can use a clustered index to quickly find the row containing the start date, and then retrieve all the adjacent rows in the table until the end date is reached.

Columns that change frequently This will cause the whole row to move, because SQL Server must save the data values in the row in physical order. Special attention should be paid to this point, because in a transaction processing system with a large amount of data, the data is changeable. Key values in clustered indexes are used as lookup keys by all nonclustered indexes, so they are stored in leaf entries of each nonclustered index.

Baidu encyclopedia-nonclustered index

Baidu Encyclopedia-Cluster Index

Baidu Encyclopedia-Unique Index