Indexes with included columns

SQL Server 2005 Books Online (November 2008) index with inclusive columns

In SQL Server 2005, you can extend the functionality of nonclustered indexes by adding non-key columns to the leaf level of a nonclustered index. By including Non-key columns, you can create a nonclustered index that overrides 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. The database engine does not consider index key columns or index key sizes when they are computed.

An index with a containing Non-key column can significantly improve query performance when all columns in the query are included as key columns or Non-key columns in the index. This enables performance improvement because the query optimizer can find all column values in the index, and does not access tables or clustered index data, thereby reducing disk I/O operations.

Attention:
When an index contains all the columns that a query references, it is often referred to as an "overwrite query."

Key columns are stored in all levels of the index, and non-key columns are stored only in the leaf level.  For more information about index levels, see table organization and indexing organization. Use inclusive columns to avoid size limits

You can include Non-key columns in a nonclustered index to avoid exceeding the current index size (the maximum number of key columns is 16 and the maximum index key size is 900 bytes). Non-key columns are not considered when the database engine calculates the number of index key columns or the index key size.

For example, suppose you want to index the following in the Document table for the AdventureWorks sample database:

Title nvarchar (50)

Revision nchar (5)

FileName nvarchar (400)

Because each character of the nchar and nvarchar data type requires 2 bytes, the index containing the three columns will exceed the size limit of 900 bytes by 10 bytes (455 * 2). Using the INCLUDE clause of the CREATE index statement, you can define the index key as (Title, Revision) and define 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 columns you want. The following statement creates such an index. Copy Code

Use AdventureWorks;
Go
CREATE INDEX ix_document_title       
on Production.document (Title, Revision)       
INCLUDE (FileName);       

indexing criteria with included columns

When designing a nonclustered index with a included column, consider the following guidelines: Define a Non-key column in the include clause of the CREATE INDEX statement. You can define Non-key columns only for nonclustered indexes on tables or indexed views. All data types are allowed except text,ntext , and image . Exact or imprecise deterministic computed columns can be inclusive columns. For more information, see Creating indexes for computed columns. As with key columns, computed columns derived from the image,ntext , and text data types can be used as non-key (inclusive) columns, as long as the computed column data type is allowed as a non-key indexed column. You cannot specify a column name in both the INCLUDE list and the key column list.  The column names in the INCLUDE list cannot be duplicated. The column size guideline must define at least one key column. The maximum number of Non-key columns is 1023 columns. That is, the largest number of table columns minus 1. Index key columns (excluding non-keys) must adhere to the existing index size (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, thevarchar (max) column is limited to 2 GB. Column modification Guidelines

When you modify a table column that is defined as a included column, you are limited by the following restrictions: You cannot delete a Non-key column from a table unless you delete the index first. You cannot make other changes to a Non-key column except for the following changes:
Change the nullability of the column from not NULL to NULL. Increase the length of the varchar,nvarchar , or varbinary columns.

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

Design Recommendations

Re-design a nonclustered index with a large index key size so that only column columns for search and lookup are listed. Sets all other columns that overwrite the query to include non-key columns. This will have all the columns that are required to overwrite the query, but the index key itself is small and highly efficient.

For example, suppose you want to design an index that overrides the following query. Copy Code

Use AdventureWorks;
Go
SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode from
person.address
WHERE PostalCode BETWEEN n ' 98000 ' and n ' 99999 ';

To overwrite a query, you must define each column in the index. Although you can define all columns as key columns, the key size is 334 bytes. Because the only column that is actually used as a search condition is the PostalCode column (length 30 bytes), a better index design should define PostalCode as a key column and contain all the other columns that are not key columns. The following statement in

Creates an index that overrides the query with a containing column. Copy code

use AdventureWorks; Go to CREATE INDEX ix_address_postalcode on person.address (PostalCode) INCLUDE (AddressLine1, AddressLine2, Ci       Ty, StateProvinceID); 

  Performance Considerations

Avoid adding unnecessary columns. Adding too many indexed columns (key or Non-key columns) has the following effect on performance: fewer indexes will fit on one page. This will increase I/O and reduce the cache efficiency. More disk space is required to store the index. In particular, varchar (max) , nvarchar (max) , varbinary (max) , or XML Adding a data type as a Non-key index column can significantly increase disk space requirements. This is because the column values are copied to the index leaf level. Therefore, they reside both in the index and in the base table. Index maintenance may increase the time required to perform modification, insert, UPDATE, or delete operations on the underlying table or indexed view.

You should determine whether the increase in query performance when modifying data exceeds the performance impact and whether additional disk space requirements are required. For more information about evaluating query performance, see Query optimization.

The understanding in its own application: Select A1,a2,a3 from B where b1=1 and b2=2 and b3=3 the order by Addtime Desc,id desc

Need to establish such an index "Addtim E desc,id desc,b1,b2,b3 ", contains columns with" A1,a2,a3 "