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.



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




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.



Like us on FaceBookJoin the fastest growing SQL Server group on FaceBookFollow me on TwitterFollow me on FaceBook