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

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

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

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 FaceBook | Join the fastest growing SQL Server group on FaceBook | Follow me on Twitter | Follow me on FaceBook