SQL Server Key Lookup Operator – Part 4

Hi Geeks,

Last three days we have been discussing on SQL Server Key Lookup operators and if you are reading this post ahead of them, I recommend you to read Part1 , Part2 , and Part3 first to have a better understanding.

As mentioned in previous post, presence of a Key Lookup operator is an indication that query optimizer might benefit from presence of a covering/included index. Today, we will revisit our query then create a covering index to include required columns and check if there are any changes to the execution plan.

USE [AdventureWorks2012]

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

KeyLookup

KeyLookupToolTip

Looking at key lookup ToolTip, it is clear that to retrieve AddressLine1 and PostalCode column SQL Server optimizer has to use index key PK_Address_AddressID causing key lookup operation. Let us now include these columns in IX_Address_StateProvinceID used in seek operation (see screenshot below from index seek nonclustered).

NCISeekToolTip

USE [AdventureWorks2012]

--Step 1 (run first)
CREATE NONCLUSTERED INDEX IX_Address_AddrPostalCode
ON Person.Address (StateProvinceID)
INCLUDE (AddressLine1, PostalCode)

--Step 2 (run only after Step 1)
SELECT Address.AddressID, Address.AddressLine1, Address.StateProvinceID, Address.PostalCode
FROM Person.Address
WHERE Address.StateProvinceID = 10

NCISeek

ModifiedNCISeekToolTip

Key lookup disappeared from the query plan and we have index seek nonclustered. SQL optimizer is able to find all the required columns in index key IX_Address_AddrPostalCode now and no additional lookup required here.

I hope you liked this short series on key lookup but we have so much to explore on One operator a day series, stay tuned.

Happy learning.

Regards,

Kanchan

Like us on FaceBookJoin the fastest growing SQL Server group on FaceBookFollow me on TwitterFollow 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 *