Can someone explain this two - Index Key Column VS Index Included Column?
Currently, I have an index that has 4 Index Key Column and 0 Included Column.
Thanks
Index key columns are part of the b-tree of the index. Included columns are not. 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).
An index with included columns can greatly improve query performance because all columns in the query are included in the index; The query optimizer can locate all columns values within the index without accessing table or clustered index resulting in fewer disk I/O operations.
The Include Clause. The include clause allows us to make a distinction between columns we would like to have in the entire index (key columns) and columns we only need in the leaf nodes ( include columns). That means it allows us to remove columns from the non-leaf nodes if we don't need them there.
An index key is a column, an ordered collection of columns, or an expression on which you define an index. Db2 uses an index key to determine the order of index entries. Good candidates for index keys are columns or expressions that you use frequently in operations that select, join, group, and order data.
Index key columns are part of the b-tree of the index. Included columns are not.
Take two indexes:
CREATE INDEX index1 ON table1 (col1, col2, col3) CREATE INDEX index2 ON table1 (col1) INCLUDE (col2, col3)
index1
is better suited for this kind of query:
SELECT * FROM table1 WHERE col1 = x AND col2 = y AND col3 = z
Whereas index2
is better suited for this kind of query:
SELECT col2, col3 FROM table1 WHERE col1 = x
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).
In the second query, index2
acts as a covering index. SQL Server doesn't have to hit the base table at all, since the index provides all the data it needs to satisfy the query. index1
could also act as a covering index in this case.
If you want a covering index, but don't want to add all columns to the b-tree because you don't seek on them, or can't because they aren't an allowed datatype (eg, XML), use the INCLUDE clause.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With