Charm of include in SQL Server index (indexed with included columns)

Before opening a text, let's start with a few concepts.

"Overwrite Query"

When an index contains all the columns that a query references, 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 indexed key value + clustered index, then Bookup Lookup does not occur because the desired data is found when the index entry is found, and there is no need to go to the data row to find it. This situation is called index overlay;

"Composite Index"

A single index is relative to a composite index, and the index contains only one field, so the composite index is an index that contains two or more fields;

"Non-key columns"

The key column is the column that is included in the index, but the Non-key column is the column outside the index;

Let's start today's theme

"Summary 1"

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.

Description: First: only for nonclustered indexes; second: There is a performance boost compared to the composite index because the size of the index becomes smaller;

"Summary 2"

Key columns are stored in all levels of the index, and non-key columns are stored only in the leaf level.

Description: This is reflected in the inclusion of a relationship that is not included. For more information about index levels, see table organization and indexing organization.