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?


1 Answer 1


A simple test with a little over 1M values


CREATE TABLE dbo.FOO([A] int , [B] int,[C] int, [D] int, [E] int);

INSERT INTO dbo.FOO([A]  , [B] ,[C] , [D] , [E] )
GO 1000

GO 10

INSERT INTO dbo.FOO([A]  , [B] ,[C] , [D] , [E] )
VALUES(1,2,1,1,1); --one matching record

This dataset is not a real world dataset, as aside from one record, all the other records are the same.

Amount of rows

select COUNT(*) from dbo.FOO
   (No column name)


Select A, B, C, D, E from dbo.FOO where A = 1 and B = 2;

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.

enter image description here


  • You cannot add a column as both the key column and the included column
  • Some datatypes cannot be added to the key column but could be included (all datatypes except text, ntext, and image)

    More info

Example of a key lookup happening as a result of a different query

create Nonclustered index IX_Foo_2 on dbo.FOO ([B]) include ([C], [D], [E]);

Select A, B, C, D, E from dbo.FOO where  B = 2;

A key lookup to get [A] happens, but it is not very expensive (one row).

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.