Tips for using indexes in SQL Server

In SQL Server, to optimize the query performance, we sometimes need to index the data table to quickly narrow the query scope based on the query requirements and avoid full table scanning.

There are two types of indexes: clustered index (also called clustered index and clustered index) and non-clustered index, also known as non-clustering index and non-cluster index ).

A clustered index can only have one table. It is created when the primary key is created by default. It specifies the physical storage sequence of data in the table. We can also cancel the clustered index of the primary key, therefore, you must consider the types of queries that the database may use and the most frequently used query types, and create clustered indexes or composite clustered indexes for one or more of the most commonly used fields, it means that SQL server will sort the data columns physically in ascending (default) or descending order, so that the queried data can be quickly found.

The non-clustered index stores data in one place, and the index stores data in another place. The index points to the data storage location. The items in the index are stored in the order of the index key value, and the information in the table is stored in another order. You can use up to 249 non-clustered indexes in a table. During the query, you can search for non-clustered indexes to find the position of the data value in the table, then, retrieve the data directly from this location. This makes non-clustered indexes the best way to precisely match queries, because indexes contain entries that describe the exact location of the data value searched by the query in the table.

Therefore, we should pay attention to the following aspects when selecting to create a clustered index:

1) when a primary key is created for a table, a clustered index is automatically added for the primary key, such as an automatically numbered field. We do not need to waste the clustered index on the primary key, unless you only query by primary key, the clustered index is set to the field with the highest frequency of query by condition or the field with the highest combination.

2) The index should be created based on the actual application requirements, not by creating an index on any field to increase the query speed. The establishment of clustered indexes follows the following principles:

L contains a large number of non-repeated values.

L use the following operators to return the query of a range value: Between,>, >=, <, and <=.

L columns that are continuously accessed.

L returns a query of a large result set.

L columns frequently accessed by queries using join or group by clauses. Generally, these columns are foreign key columns. Index the columns specified in the order by or group by clause, so that SQL server does not have to sort the data because these rows have been sorted. This improves the query performance.

L OLTP applications, which require fast single-row Lookup (usually through primary keys ). Create a clustered index on the primary key.

For example, in the bank transaction log, an aggregate index is set up for the transaction date. The data is physically stored on the data page in order, and the duplicate values are also arranged together. Therefore, during range search, you can first find the start and end points of this range, and only scan the data page within this range, avoiding large-scale scanning and improving the query speed. If we create a clustered index for the field columns in the basic information table of employees, it is completely unnecessary, because the content only involves two different values: "male" and "female.

3) create an index based on common composite fields in the clustered index to form a composite index, which is usually generated when multiple primary keys are created for the table, if multiple fields always appear in the query of data in a table, these fields can be used as composite indexes, which can overwrite the index and improve the query efficiency of the where statement.

4) indexes optimize the query. However, changing the content of a table will cause the index change. Frequent data operations, such as insert, update, and delete statements, will cause the system to perform index updates at a high cost, resulting in a decline in overall performance. In general, when the query performance requirements are higher than the data maintenance performance requirements, you should try to use indexes. Sometimes, in some extreme situations where such operations on the database are frequent, you can delete the indexes first, update a large amount of data to the database table, and then re-create the index. The new index is always easy to use.

When indexes are used for a long time, there will be a lot of fragmentation, and the query performance will be affected. At this time, there are two solutions: one is to use DBCC indexdefrag to sort out index fragmentation, in addition, you can use DBCC dbreindex to reconstruct the index.

The DBCC indexdefrag command is an online operation, so the index is only available when the command is running. In addition, the operation can be interrupted without losing the completed work. The disadvantage of this method is that there is no clustered index removal/re-creation operation in reorganizing data.

Re-create a clustered index will re-organize the data. The result is that the data page is filled up. You can use the fillfactor option to configure the fill level. The disadvantage of this method is that the index is offline during the deletion/re-creation period and the operation belongs to the atomic level. If the index creation is interrupted, the index will not be re-created.

Let's take a look at the method used for index reconstruction:

Syntax DBCC dbreindex (['tablename' [, index_name [, fillfactor])

Parameter 'tablename'

Is the name of the table whose specified index is to be rebuilt. The database, owner, and table names must comply with the identifier rules. For more information, see use identifiers. If the database or owner part is provided, you must use single quotation marks (')

Enclose the entire database. Owner. table_name. If only table_name is specified, no single quotation marks are required.

Index_name is the name of the index to be rebuilt. The index name must comply with the rules of the identifier. If index_name or ''is not specified, all indexes of the table are rebuilt.

Fillfactor is the percentage of data storage space on each index page when an index is created. Fillfactor

Replace the starting fill factor as the new MO of the index or any other re-built non-clustered index (because the clustered index has been rebuilt)

Value. If fillfactor is 0, DBCC dbreindex uses the specified starting fillfactor when creating an index.

Run the following command in the query Analyzer:

DBCC dbreindex ('mytable, '', 80)

In this way, the index is rebuilt.