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.
