Index Key Column VS Index Included Column

Question

Hi there

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

Answer 1

Included columns don't form part of the key for the index, but they do exist on the index. Essentially the values will be duplicated, so there is a storage overhead, but there is a greater chance that your index will cover (i.e. be selected by the query optimizer for) more queries. This duplication also improves performance when querying, since the database engine can return the value without having to look at the table itself.

Only nonclustered indexes can have included columns, because in a clustered index, every column is effectively included.

Answer 2

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.