There are a couple of concepts to talk about before you start.
"Overwrite Query"
When an index contains all the columns referenced by a query, it is often referred to as an "overwrite query."
"Index Overlay"
If the returned data column is contained in the key value of the index, or is contained in the key value of the index + the key value of the clustered index, then Bookup Lookup does not occur, because the index entry is found, and there is no need to find the data row. In this case, it is called index overlay;
"Composite Index"
In contrast to a composite index is a single index, that is, the index contains only one field, so the composite index is the index containing two or more fields;
"Non-key column"
The key column is the column that is contained in the index, and of course the Nonkey column is the column outside the index;
Let's start with today's theme.
"Abstract 1"
In SQL Server 2005, you can extend the functionality of nonclustered indexes by adding non-key columns to the leaf level of the nonclustered index. You can create a nonclustered index that overrides more queries by including nonkey columns. This is because nonkey columns have the following advantages:
* They can be data types that are not allowed as index key columns.
* The database engine does not consider them when calculating the number of index key columns or index key sizes.
Indexes with inclusive nonkey columns can significantly improve query performance when all columns in the query are included as key or nonkey columns in the index. This enables performance gains because the query optimizer can find all column values in the index, and does not access table or clustered index data, thereby reducing disk I/O operations.
Description: First: only for nonclustered indexes; second: There is a performance improvement over the composite index, because the size of the index becomes smaller;
"Abstract 2"
The key columns are stored at all levels of the index, and non-key columns are stored only in the leaf level.
Description: This is expressed as a relationship that contains and does not contain. For more information about index levels, see table organization and Index organization.
"Abstract 3"
Use containment columns to avoid size limits
You can include non-key columns in a nonclustered index 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). 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 types 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 the FileName as a nonkey column. 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 such an index.
Note: When you set a nvarchar (500) field as the primary key, you can see that you cannot exceed the 900-byte hint. In general, we are not very able to do these operations, so the error is not common, perhaps you have seen.
The size of a data page is 8k, so we reasonably set the size of each field, do not waste too much space, so that the query is also beneficial, the include is a better solution to the index and space problems, although those include data will occupy space.
Although you can set include, try not to use too many fields as nonkey columns that are included in the index.
"Abstract 4"
Index criteria with inclusive columns
When you design a nonclustered index with an included column, consider the following guidelines:
* Define non-key columns in the INCLUDE clause of the CREATE INDEX statement.
* Non-key columns can be defined only on nonclustered indexes of tables or indexed views.
* All data types are allowed except text, ntext, and image.
* Deterministic computed columns that are accurate or imprecise can be inclusive columns. For more information, see Create an index on a computed column.
* 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 nonkey index column.
* Column names cannot be specified in both the INCLUDE list and the key column list.
* column names in the INCLUDE list cannot be duplicated.
Description: Include cannot be used in a clustered index. The next two points, it's hard to imagine in practice that there is a need to put a repeating column in an index. If a friend has met such a demand can tell some, greatly appreciated. If there is a different column name (in fact, save is the same value) to solve the problem??
"Abstract 5"
Column size guidelines
* At least one key column must be defined. The maximum number of non-key columns is 1023 columns. That is, the maximum number of table columns minus 1.
* Index key columns (excluding non-keys) must adhere to the existing 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 nonkey columns is limited only by the size of the column specified in the INCLUDE clause, for example, the varchar (max) column is limited to 2 GB.
Description: varchar (max) is defined only after 2005, so these values are also valid for versions after 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 already defined as an included column, you are constrained by the following:
* Non-key columns cannot be removed 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 nullability of the column 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.
Description: These tiny things have not been noticed. So to record down, used to "self-defense", hehe.
"Abstract 7"
Design recommendations
Re-design a nonclustered index with a large index key size so that only columns that are used for search and lookup are column keys. Sets all other columns that overwrite the query to include non-key columns. This will have all the columns required to overwrite the query, but the index key itself is small and efficient.
Note: This means that the field that is used in the following condition query is the key column of the index, and the field that needs to be returned is the non-key column that the index contains.
If the where is two or more than two predicates, the index can be created as a composite index. It was naïve to think that the fields to be returned can only be entered in the composite index, regardless of whether it is used to make predicates. See this article, only have the feeling of enlightened.
"Abstract 8"
Use AdventureWorks;
GO
CREATE INDEX Ix_address_postalcode
On Person.Address (PostalCode)
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);
Description: This is the use of the syntax of include, in the design of the table in the index design is no way to choose;
"Abstract 9"
Performance considerations
Avoid adding unnecessary columns. Adding too many indexed columns (key columns or nonkey columns) has the following effects on performance:
* Fewer index lines can be accommodated on a page. This increases the I/O and reduces cache efficiency.
* More disk space is required to store the index. In particular, adding varchar (max), nvarchar (max), varbinary (max), or XML data types as Nonkey index columns 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 modifications, inserts, updates, or deletions on the underlying table or indexed view.
You should determine whether the increase in query performance when modifying data exceeds the impact on performance and whether additional disk space requirements are required. For more information about evaluating query performance, see Query optimization.
Description: "This is because the column values are copied to the index leaf level" is a good illustration of the physical storage structure and principle.
"Image Resolution"
It also explains why an index with an included column cannot be established in a clustered index because the leaf layer of a nonclustered index consists of an index page rather than a data page, which is the physical storage of clustered and nonclustered indexes, and the order and storage of the clustered index is the order and storage structure of the base table.
"An example"
SELECT username,password,realname,mobile,age from bw_users WHERE UserName = XXX and age = XX
Description
- This is a very common query statement, how can we improve the efficiency of the query?
- First we take a look at the predicate, this statement is by username = XXX and age = XX as a condition, then we should establish a composite index, also known as a composite index, note the position of the key column in the index, first username after age;
- In fact, the above is a nonclustered index, then we can put password,realname,mobile these three columns as an index containing columns;
- Therefore, the final is to establish a username and age as a key column, password,realname,mobile as a non-key column of the nonclustered index;
- Generally speaking, the user table of our system is not very large, so the optimization does not have the obvious effect, if interested can use large table for performance testing;
Other
- One thing I'm surprised about is why is the "included columns" not available when modifying a table? Can I write this class index only through commands?
- Another point I would like to say, Microsoft's MSDN is indeed the best learning tool, the search on the internet is a lot of things are repeated, and said not all, but can speak relatively simple, popular only. So take a look at MSDN for free. This is a statement to yourself. Oh.
Http://www.cnblogs.com/gaizai/archive/2010/01/11/1644358.html
Charm of include in SQL Server index (index with included column)
