Key Lookup in SQL server

A Lookup happen when a query uses a non-clustered index but the index does not include the all column needed for the query. Unlike a RID Lookup, which occurs in the heap table, but key lookup happens when the table has a cluster index. SQL server must go back to the cluster index (using the cluster key) to fetch the missing column that are not included in the non-cluster index.

See the example.

Creating the table

create table Key_Lookup_Test ( Key_Lookup_Test_ID int identity(1,1) not null primary key, col_num1 varchar(20), col_num2 varchar(20), col_num3 varchar(20) )

Creating a non-clustered index on this table.

create nonclustered index IX_01_Key_Lookup_Test_col_num1 on Key_Lookup_Test(col_num1)

Inserting 10k Records.

declare @i int=1; while @i<=10000 begin insert into Key_Lookup_Test(col_num1,col_num2,col_num3) select 'col_num1'+cast(@i as varchar(10)),                                 'col_num2'+cast(@i as varchar(10)),                                 'col_num3'+cast(@i as varchar(10)) set @i=@i+1; end;

Records inserted successfully.

Let’s on the execution plan and run the below query.

select key_Lookup_Test_id,col_num1,col_num2,col_num3 from key_Lookup_Test where col_num1='col_num110';

See the result.

Key lookup happen.

See the execution time.

It took 42 MS .

Let’s drop the existing index and creating the new index with included columns.

drop index key_Lookup_Test.IX_01_key_Lookup_Test_col_num1 create nonclustered index IX_01_Key_Lookup_Test_col_num1 on key_Lookup_Test(col_num1) include (key_Lookup_Test_id,col_num2,col_num3)

Now running the same query. 

Key lookup gone.

See the execution time.

It took 1 MS.