Include charm in indexes (include index ))

Before I start, I want to talk about several concepts.

Overwrite Query]

When an index contains all the columns referenced by a query, it is usually called "Overwrite query ".

[Index coverage]

If the returned data column is included in the index's key value, or included in the index's key value + clustered index's key value, no bookup lookup occurs because the index item is found, the required data has been found, and there is no need to go to the data row again. In this case, it is called index overwrite;

 

[Compound index]

Compared with composite indexes, a single index contains only one field. Therefore, composite indexes are indexes that contain two or more fields;

[Non-key column]

A key column is the column contained in the index. Of course, a non-key column is a column outside the index;

 

Next we will start today's theme

【Abstract】 1]

In SQL Server 2005 You can extend the non-clustered index function by adding non-key columns to the leaf level of the non-clustered index. By including non-key columns, you can create non-clustered indexes that overwrite more queries. This is because non-key columns have the following advantages:
* They can be data types that are not allowed as index key columns.
* When calculating the number of index key columns or index key size, the database engine does not consider them.
When all the columns in the query are included as key columns or non-key columns in the index, indexes with inclusive non-key columns can significantly improve the query performance. This improves the performance, because the query optimizer can find all column values in the index; does not access the table or clustered index data, thus reducing disk I / O operation.

Note: first, it can only be usedNon-clustered index; Second: Compared with composite index, the performance is improved because the index size is smaller;

 

【Abstract】 2]

Key columns are stored at all levels of the index, rather than at the leaf level.

Note: This shows the relationship between inclusion and non-inclusion. For more information about the index level, see Table organization and index organization.

 

【Abstract】 3]

Use include columns to avoid size restrictions
You can include non-key columns in a non-clustered index to avoid exceeding the current index size limit (the maximum number of key columns is 16 , The maximum index key size is 900 Bytes ). The Database Engine calculates the number of index key columns or index key size hours, regardless of Non-key columns.
For example, if you want to create an index for the following columns in the Document Table of the adventureworks sample database:
Title nvarchar ( 50 )
Revision nchar ( 5 )
Filename nvarchar ( 400 )
Because each character of the nchar and nvarchar data types needs 2 So the Index containing these three columns will exceed 900 Byte size limit 10 Bytes ( 455   *   2 ). Using the include clause of the create index statement, you can define the index key as (title, Revision) and filename as a non-key column. In this way, the index key size will be 110 Bytes ( 55   *   2 ), And the index will still contain all required columns. The following statement creates an index.

Note: When you set an nvarchar (500) field as the primary key, you will be able to see a prompt that cannot exceed 900 bytes. Generally, we do not perform these operations, so the error prompt is not common. You may have seen it before.

The size of a data page is only 8 KB. Therefore, we can reasonably set the size of each field and do not waste too much space. This is also good for queries, this include statement effectively solves the index and space problems, although the include data also occupies space.

Although you can set include, do not use too many fields as non-key columns contained in the index.

 

【Abstract]

Index criteria with inclusive Columns
Consider the following guidelines when designing non-clustered indexes with contained columns:
* Define non-key columns in the include clause of the create index statement.
* Only non-clustered indexes in the table or index view can define non-key columns.
* All data types are allowed except text, ntext, and image.
* Exact or inaccurate deterministic calculation columns can be inclusive columns. For more information, see create an index for a calculated column.
* Like a key column, a computed column derived from the image, ntext, and text data types can be used as a non-key (inclusive) column) column.
* You cannot specify a column name in both the include list and key column columns.
* The column names in the include list cannot be repeated.

Note: Include cannot be used in clustered indexes. In practice, it is hard to imagine that duplicate columns should be placed in an index. I am very grateful if you have such a requirement. Can I solve this problem through different column names (in fact, the same value is saved ??

 

【Abstract】 5]

Column size criterion
* At least one key column must be defined. The maximum number of non-key columns is 1023 Column. That is, the maximum number of columns in the table is reduced. 1 .
* The index key column (excluding non-key columns) must comply with the size limit of the existing index (the maximum number of key columns is 16 The total index key size is 900 Bytes ).
* The total size of all non-key columns is limited only by the size of the columns specified in the include clause. For example, the size of the varchar (max) column is 2 GB.

Note:Varchar (max)This definition is available only after 2005, so these values also take effect for Versions later than 2005.

The maximum number of table columns is 1024.

The maximum number of non-key columns is:1023

 

【Abstract】 6]

When you modify a table column that is defined as an include column, you must be subject to the following restrictions:
* Non-key Columns cannot be deleted from the table unless the index is deleted first.
* You cannot make other changes to non-key columns except for the following changes:
O change the column's null property from not null to null.
O increase the length of the varchar, nvarchar, or varbinary columns.
* These column modification restrictions also apply to index key columns.

Note: These tiny things have never been noticed. So we need to record it for "anti-body.

 

【Abstract】 7]

Design Recommendations
Redesign non-clustered indexes with a large index key size so that only the columns used for search and search are key columns. Set all other columns that overwrite the query to include non-key columns. In this way, all the columns required for the query will be overwritten, but the index key itself is small and efficient.

 

Note: in other words, the field of the condition query after the common where clause is used as the index.Key ColumnAnd the fields to be returned are included in the index.Non-key column.

If the WHERE clause contains two or more predicates, the index can be created as a composite index. In the past, I naively thought that the fields to be returned can only be included in the composite index, regardless of whether they will be used as predicates. See this articleArticleAnd then the feeling of being open.

 

【Abstract]

Use adventureworks;
Go
Create index ix_address_postalcode
On person. Address (postalcode)
Include (addressline1, addressline2, city, stateprovinceid );

Note: This is the use of the include syntax. There is no way to choose the index design in the design of the table;

 

【Abstract】 9]

Performance Considerations
Avoid adding unnecessary columns. Adding too many index columns (key columns or non-key columns) will have the following impact on performance:
* There will be fewer indexes on one page. This will make I / O increases and reduces cache efficiency.
* More disk space is required to store indexes. In particular, adding varchar (max), nvarchar (max), varbinary (max), or XML data types to non-key index columns significantly increases disk space requirements. This is because the column value is copied to the index leaf level. Therefore, they both reside in the index and the base table.
* Index maintenance may increase the time required to modify, insert, update, or delete a base table or index view.
You should determine whether the performance improvement during data modification exceeds the impact on performance and whether additional disk space requirements are required. For more information about how to evaluate query performance, see query optimization.

Note:"This is because the column value is copied to the index leaf level.This statement illustrates the physical storage structure and principle.

 

 

Indexing of include columns: http://msdn.microsoft.com/zh-cn/library/ms190806%28SQL.90%29.aspx

 

From: http://www.cnblogs.com/gaizai/archive/2010/01/11/1644358.html? Login = 1 # commentform