The include function of the create index statement

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:
When an index contains all the columns referenced by a query, it is usually called "Overwrite query ".

Key columns are stored at all levels of the index, rather than at the leaf level. For more information about the index level, see Table organization and index organization.

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
The nvarchar data type requires two bytes for each character. Therefore, indexes containing these three columns will exceed the size limit of 900 bytes, with 10 more bytes (455 *
2 ). Use the include clause of the create index statement to define the index key as (title, revision ).
Filename is defined 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.

Copy code
Use adventureworks;
Go
Create index ix_document_title
On production. Document (title, Revision)
Include (filename );

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.

Column size criterion
At least one key column must be defined. The maximum number of non-key columns is 1023. That is, the maximum number of columns in the table is reduced by 1.

The index key column (excluding non-key columns) must comply with the current index size limit (the maximum number of key columns is 16, and 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 varchar (max) column is limited to 2 GB.

Column modification rules
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:

Change the column from not null to null.

Increase the length of the varchar, nvarchar, or varbinary columns.

Note:
These column modification restrictions also apply to index key columns.

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.

For example, suppose you want to design an index that overwrites the following query.

Copy code
Use adventureworks;
Go
Select addressline1, addressline2, city, stateprovinceid, postalcode
From person. Address
Where postalcode between N '20160301' and 'n' 20160301 ';

If
To overwrite the query, you must define each column in the index. Although all columns can be defined as key columns, the key size is 334 bytes. Because the only Column Used as the search condition is postalcode
Columns (30 bytes in length), so a better index design should define postalcode as a key column and include all other columns as non-key columns.

The following statement creates an index that overwrites the query with an inclusive column.

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

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 increase I/O and reduce cache efficiency.

Yes
You need more disk space to store indexes. In particular, convert varchar (max), nvarchar (max), varbinary (max), or XML
Adding a non-key index column to the Data Type 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.