The charm of include in SQL Server indexes (1)

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

What is an index with an inclusive column? Please see Official explanation: http://msdn.microsoft.com/zh-cn/library/ms190806%28SQL.90%29.aspx

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;

Composite 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;

The following is the topic summary of today]

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 locate all column values in the index, and does not access the table or clustered index data, thus reducing disk I/O operations.

Note: first, it can only be used for non-clustered indexes. Second, it improves the performance of composite indexes 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 non-clustered indexes to avoid exceeding the current index size limit (the maximum number of key columns is 16, and 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 requires two bytes, the Index containing these three columns will exceed the size limit of 900 bytes to 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 the 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 4]

Index criteria with inclusive Columns

Consider the following guidelines when designing non-clustered indexes with contained columns:

* The INCLUDE clause of the create index statement defines non-key columns.

* Only non-clustered indexes in the table or index view can define non-key columns.

* All data types except text, ntext, and image are allowed.

* Exact or inaccurate deterministic calculation columns can all 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 as long as the data type of the computed column is allowed to be used as a non-key index column) column.

* You Cannot specify a column name in both the INCLUDE list and key column tables.

* 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 ??