Assume that I have a table Foo with columns A, B, C, D, and E. All are integers. Let's assume a million records, and that we're on SQL Server 2017.
The following index exists:
create Nonclustered index IX_Foo on Foo ([A], [B]) include ([C], [D], [E])
If I run the following query:
Select A, B, C, D, E from Foo where A = 1 and B = 2
Will this query trigger a Key Lookup in Foo because A and B are not included in the index's Include listing, or will SQL Server realize that A and B are located in the key itself and avoid a Key Lookup?
Best Answer
A simple test with a little over 1M values
Data
This dataset is not a real world dataset, as aside from one record, all the other records are the same.
Amount of rows
Query
No key lookup
Which is normal as this index is perfect considering the query and the dataset.
key column <> included column
The main difference between the key columns and included columns is that these key columns are ordered [A] --> [B] (B-Tree) and the other columns [C], [D], [E] are not ordered (leaf level).
As a result seeking only on [A] is possible, seeking on [AB] is possible, but seeking only on [B] will create a residual predicate if the index is used.
Additionally
Some datatypes cannot be added to the key column but could be included (all datatypes except
text
,ntext
, andimage
)More info
Example of a key lookup happening as a result of a different query
A key lookup to get [A] happens, but it is not very expensive (one row).