Index-non-clustered index design guide-reprinted

The non-clustered index contains the index key value and the row locator pointing to the table data storage location. For more information about the non-clustered index architecture, see the non-clustered index structure.

You can create multiple non-clustered indexes on a table or index view. Generally, designing a non-clustered index is to improve the performance of frequently used queries without a clustered index.

Similar to using the index in the book, the query optimizer first searches for non-clustered indexes to find the position of the data value in the table, and then retrieves the data directly from the position. this makes the non-clustered index the best choice for exact match queries, because the index contains items that indicate the exact position of the Data searched by the query in the table. for example. the Query Optimizer may use a non-clustered index ix_person_lastname_firstname_middlename. It uses lastname as its own key column. the query optimizer can quickly find all the items in the index that match the specified lastname. Each index item points to the accurate pages and rows in the table or clustered index, where the corresponding data can be found. after the query optimizer finds all the items in the index, it can directly go to the correct page and row for data retrieval.

Database considerations

Pay attention to the database features when designing non-clustered indexes.

• Databases or tables with low Update Requirements but large amounts of data can benefit from many non-clustered indexes to improve query performance. compared with full-table non-clustered indexes, creating a screening index for a well-defined data subset can improve query performance, reduce index storage overhead, and reduce index maintenance overhead.

  Decision-making support system applications and databases that mainly contain read-only data can benefit from many non-clustered indexes. The query optimization tool has more available indexes to determine the fastest access method, in addition, the low update feature of the database means that index maintenance will not reduce performance.

• Online transaction processing applications and databases that contain a large number of update Tables should avoid using too many indexes. In addition, the index should be narrow, that is, the fewer columns, the better.

  If a table has a large number of indexes, the performance of insert, update, delete, and merge statements will be affected, because when the data in the table is changed, all indexes must be adjusted as appropriate.

Query considerations

Before creating a non-clustered index, you should first understand how to access data and consider using a non-clustered index for queries with the following attributes:

• Use the join or group by clause.

  Multiple non-clustered indexes should be created for the columns involved in join and group operations, and a clustered index should be created for any foreign key column.

• Query of large result sets is not returned.

  Create a filter index to overwrite the query that returns a well-defined subset of rows from a large table.

• Contains columns that are frequently included in the search criteria (for example, return a fully matched WHERE clause.

Column considerations

Consider columns with one or more attributes:

• Overwrite Query

  When the index contains all the columns in the query, the performance can be improved. The query optimizer can find all the column values in the index without accessing the table or clustered index data, this reduces disk I/O operations. you can use an index that contains a column to add an overwrite column, instead of creating a wide index key. for more information, see index with included Columns

  If the table has a clustered index, the columns defined in the clustered index are automatically appended to the end of each non-clustered index on the table, which generates overwrite queries, instead of specifying clustered index columns in non-clustered index definitions. for example, if a table has a clustered index on column C, the non-clustered indexes of Column B and column A will have their own key value columns B, A, and C.

• A large number of non-repeated values, such as the combination of surnames and names (provided that the clustered index is used for other columns ).

  If there are only a few non-repeated values, such as only 1 and 0, most queries do not use indexes, because table scanning is generally more effective. for this type of data, you should consider creating a filtering index for non-duplicate values that only appear in a few rows. for example, if most of the values are 0, the query optimizer can filter and query data rows containing 1.

Index options

When creating a non-clustered index, you can specify several index options. Pay special attention to the following options:

• Fillfactor

• Online

For more information, see SET index options

Index-non-clustered index design guide-reprinted