Http://www.cnblogs.com/gaizai/archive/2010/01/11/1644358.html
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 locate all column values in the index, and does not access the table or clustered index data, thus reducing disk I/O operations. Copy code
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 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 bytes, the Index containing these three columns will exceed
Size Limit of 900 bytes
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. Copy code
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:
* 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. Copy code
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 columns. That is, the maximum number of columns in the table is reduced.
1.
* The index key column (excluding non-keys) 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. Copy code
Note: varchar (max) is defined after 2005, so these values are effective only for Versions later than 2005.
The maximum number of table columns is 1024.
Maximum number of non-key columns: 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. Copy code
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. Copy code
Note: in other words, the field of the condition query after the common where clause is used as the key column of the index, and the field to be returned is used as the non-key column included in the index.
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. Seeing this article, I feel very open.
【Abstract]
Use adventureworks;
Go
Create index ix_address_postalcode
On person. Address (postalcode)
Include (addressline1, addressline2, city, stateprovinceid); copy the code
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 rows of indexes on one page. This will increase I/O and reduce 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 the 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.
Copy code
Note: "This is because the column value is copied to the index leaf level" describes the physical storage structure and principle.
[Image Parsing]
It also explains why you cannot create an index with an inclusive column in a clustered index, because the leaf layer of a non-clustered index is composed of an index page rather than a data page, this requires the physical storage of clustered and non-clustered indexes. The ordered sorting and storage of clustered indexes are the sequence and storage structure of the Base table.
[Example]
Select username, password, realname, mobile, age from bw_users where username = xxx and age = xx
Note:
- This is a common query statement. How can we improve the query efficiency?
- First, let's take a look at the predicates. This statement uses username = xxx and age = XX as the condition, so we should create a composite index, also known as composite index, pay attention to the location of the key column in the index, first username and then age;
- In fact, the above is a non-clustered index, so we can use the password, realname, and mobile columns as index inclusion columns;
- Therefore, a non-clustered index with username and age as key columns, password, realname, and mobile as non-key columns is created;
- Generally, the user tables in our system are not very large, so such optimization cannot be very effective. If you are interested, you can use large tables for performance testing;
[Others]
- I am wondering why [the columns included] are unavailable when I modify a table? Can I write such indexes only by using commands?
- In addition, I would like to say that Microsoft's msdn is indeed the best learning tool, and many of the items found on the Internet are repeated and incomplete, however, it is simple and plain. So if you are free, check msdn. This sentence is for yourself. Haha.
Official explanation: indexing of include columns
------------------- Gorgeous split line -------------------
By: Listening to wind and rain
Source: http://gaizai.cnblogs.com/
Copyright: The copyright of this article is shared by the author and the blog
Reprinted: you are welcome to repost it, but remember to leave the purchase money
Email: gaizai@126.com
Motto: You don't like it because you don't.
