SQL Server: Index and Include Columns 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?
A simple test with a little over 1M values

Data

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

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

INSERT INTO dbo.FOO
SELECT * FROM dbo.FOO;
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)
1024001


Query

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 &lt;> 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


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).