SQL Server index-nonclustered index < seventh >

I. Non-clustered index maintenance

The row locator value for a nonclustered index keeps the same clustered index value, even after the clustered index column is physically relocated.

To optimize this maintenance overhead, SQL Server adds a pointer to the old data page to point to the new data page after the page is split, instead of updating the row locators for all related nonclustered indexes. This reduces the maintenance overhead of the nonclustered index, but increases the navigation overhead from the nonclustered index rows to the data rows, because a connection between the old data page and the letter data page is added. Therefore, the clustered index as a row locator reduces the overhead associated with nonclustered indexes.

second, define bookmark search

A lookup is required when a query request is not a column that is part of a nonclustered index selected by the optimizer. This is a keyword lookup for a clustered index, and the heap heap table is a RID lookup. These lookups are collectively derived from the old definition name-bookmark lookup. This lookup reads the corresponding data row from the table based on the row locator value of the nonclustered index, and in addition to the logical read operation on the index page, a logical read on the data page is required. However, if the query requires a column in the index, then you do not need to access the data page. This is known as an overlay index.

These bookmark lookups are the reason why large result sets are best used with clustered indexes. A clustered index does not require a bookmark lookup because the leaf page is the data page.

III. Recommendations for nonclustered indexes

Because a table can have only one clustered index, you can use the flexibility of multiple nonclustered indexes to help improve performance. The determinants used by nonclustered indexes are described below.

　　1. When to use nonclustered indexes

Nonclustered indexes are most useful when a small number of rows need to be read from a large table, and the cost of the bookmark lookup increases proportionally as the number of rows that need to be retrieved increases. In order to retrieve a small number of rows from a table, the index column should be highly selective.

Scenarios where nonclustered indexes are appropriate:

• column has high selectivity;
• Get small amounts of data based on columns;
• narrow columns;
• Columns are often sorted by grouping;

The following is a case where a clustered index is not appropriate, but a clustered index can also be used

• Columns that are frequently updated;
• Wide type column;

Frequent updates to clustered indexes are very resource-intensive because they affect the order of the tables and also affect other indexes, and the cost of nonclustered indexes on frequently updated columns is not as large as the clustered index. Update operations on nonclustered indexes are scoped to the base table and nonclustered indexes, and it does not affect other nonclustered indexes on the table.

The wide type column is similar, with a wide type on the nonclustered index column, although the impact is as large as the clustered index, but it should be used with care.

　　2. When not to use clustered indexes

Nonclustered indexes are not suitable for large rows of queries. Such queries use a clustered index better, and the clustered index does not require a large number of bookmark lookups, and instead of the logical reads retrieved on the indexed columns, the bookmark lookup consumes too much resources. The SQL Server query optimizer takes this overhead into account when retrieving large result sets and discards the nonclustered index accordingly.

Not suitable for non-clustered indexes: Get a large amount of data, low selectivity;

SQL Server index-nonclustered index < seventh >