SQL Server Key Lookup Operator– Part 1

Hi Friends,

Continuing from yesterday’s post on Non-clustered Index seek operation, obvious question is what happens if non clustered index does not contain all columns requested by statement(s)? To find an answer to this question, let us modify our query to return AddressLine1, PostalCode as well along with AddressID and StateProvinnceID.

USE [AdventureWorks2012]

SELECT Address.AddressID, Address.AddressLine1, Address.StateProvinceID, Address.PostalCode
FROM Person.Address
WHERE Address.StateProvinceID = 10

KeyLookup

NCISeekToolTip

SQL Server query optimizer chosen IX_Address_StateProvinceID to find the records quickly as can be seen in non-clustered index seek operation however the index doesn’t cover additional columns and for the same reason it has to use the clustered index PK_Address_AddressID on the base table to get the additional records. This is performed by SQL Server Key Lookup operator.

KeyLookUpToolTip

When we observe this operator in query plan, in simple words it means that SQL Server query optimizer cannot retrieve the rows in a single go and has to use a clustered key to fetch required rows and in our case it is PK_Address_AddressID. Following is the snapshot of indexes used here in order to fetch required data.

Indexes

We are going to explore more on this operator in next three days, stay tuned.

Regards,

Kanchan

Like us on FaceBookJoin the fastest growing SQL Server group on FaceBookFollow me on Twitter | Follow me on FaceBook

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Kanchan Bhattacharyya

Kanchan is an astute IT professional, a seasoned SQL Database Administrator with 13+ years of industry experience. A calculated risk taker with deep technical knowledge and has gained proficiency in database consulting and solutions across different environments. Kanchan holds MBA degree in IT and an International Executive MBA in Project Management. He is very passionate about SQL Server and holds MCSE (Data Platform), MCSA – SQL 2012, MCITP – SQL 2008 certifications. Currently he is focusing on cloud and latest releases of SQL Server. When not working, Kanchan likes to spend his time reading on new technical developments specifically on SQL Server and other related technologies.

View all posts by Kanchan Bhattacharyya →

Leave a Reply

Your email address will not be published. Required fields are marked *