Contents
What are included columns?
Covering Index Indexes with included columns provide the greatest benefit when covering the query. This means that the index includes all columns referenced by your query, as you can add columns with data types, number or size not allowed as index key columns.
What is a non key column?
If the primary key of a table contains only a single column, the table is automatically in third normal form. A column is a nonkey column when it is part of the primary key.
Is unique key is automatically indexed?
A unique index ensures that the values in the index key columns are unique. A unique constraint also guarantees that no duplicate values can be inserted into the column(s) on which the constraint is created. When a unique constraint is created a corresponding unique index is automatically created on the column(s).
When to use included or key columns in SQL Server?
In SQL Server 2000 and earlier, for a nonclustered index, all columns defined for an index were key columns, which meant they were part of every level of the index, from the root down to the leaf level. When a column is defined as an included column, it is part of the leaf level only. Books Online notes the following benefits of included columns:
When is a column defined as an included column?
When a column is defined as an included column, it is part of the leaf level only. Books Online notes the following benefits of included columns: They can be data types not allowed as index key columns.
Which is better included columns or index key columns?
Index key columns are part of the b-tree of the index. Included columns are not. Whereas index2 is better suited for this kind of query: In the first query, index1 provides a mechanism for quickly identifying the rows of interest. The query will (probably) execute as an index seek, followed by a bookmark lookup to retrieve the full row (s).
Can a nonkey column be included in an index?
They can be data types not allowed as index key columns. They are not considered by the Database Engine when calculating the number of index key columns or index key size. An index with nonkey columns can significantly improve query performance when all columns in the query are included in the index either as key or nonkey columns.