Index-clustered index design guide-reproduced

Clustered indexes sort and store the data rows based on the key values of data rows in the table. Each table can only have one clustered index, because the data rows can only be stored in one order. for more information about the clustered index architecture, see clustered index structure.

Almost every table defines clustered indexes for columns to implement the following functions:

• It can be used for frequently used queries.

• Provides high uniqueness. when you create a primary key constraint, a unique index is automatically created on the column. by default, this index is a clustered index, but you can specify to create a non-clustered index when creating constraints.

• It can be used for range query.

If the unique attribute is not used to create a clustered index, the database engine automatically adds a 4-byteUniqueifierColumn. If necessary, the database engine will automatically addUniqueifierValue to make each key unique. This column and column value are used internally and cannot be viewed or accessed by users.

Query considerations

Before creating a clustered index, you should first understand how data is accessed. Consider using clustered indexes for queries with the following features:

• Returns a series of values using operators (such as between,>, >=, <, and <=.

  When a clustered index is used to locate the row that contains the first value, it can ensure that the row that contains the subsequent index value is physically adjacent. For example, if a query retrieves records between a series of sales order numbers,SalesordernumberThe clustered index of a column can quickly locate the row containing the starting sales order number, and then retrieve all consecutive rows in the table until the last sales order number is retrieved.

• Returns a large result set.

• The join clause is used. Generally, the clause is a foreign key column.

• Using the order by or group by clause, the index of the column specified in the order by or group by clause can enable the database engine to not sort data because these rows have been sorted, this improves query performance.

Column considerations

Generally, the fewer columns used when defining the clustered index key, the better. Consider columns with one or more of the following attributes:

• Unique or contains many unique values

  For example, an employee ID uniquely identifies an employee.EmployeeidThe clustered index or primary key constraint of a column improves the query performance of searching employee information based on employee ID numbers.Lastname,Firstname,MiddlenameCreate a clustered index for a column because it is often used to group and query employee records in this way, and the combination of these columns also provides a high degree of discrimination.

• Accessed in order

  For example, the product ID uniquely identifies the adventureworks2008r2 DatabaseProduction. ProductProducts in the table. The query (for example, where productid between 980 and 999) specified in the specified sequence will beProductidThis is because rows are stored in the sort order of the key column.

• Because the column is unique in the table, it is defined as identity.

• It is often used to sort the retrieved data in a table.

  Clustering by this column (physical sorting) is a good way to save the cost of sorting operations each time you query this column.

Clustered indexes are not applicable to columns with the following attributes:

• Columns frequently changed

  This will move the entire row, because the database engine must retain the data values in the row in physical order. This is especially important because the data in the large-capacity transaction processing system is usually variable.

• A wide key is a combination of several or several large columns. all non-clustered indexes use the key values in the clustered index as the search key. Any non-clustered index defined for the same table will increase a lot because the non-clustered index item contains the clustered key, it also contains key columns defined for this non-clustered index.

SET index options

When creating a clustered index, you can specify several index options. Because clustered indexes are usually large, pay special attention to the following options:

• Sort_in_tempdb

• Drop_existing

• Fillfactor

• Online

For more information, see SET index options.

Index-clustered index design guide-reproduced