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.
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
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 "