SQL Server Key Lookup Operator – Part 2

Hi Geeks,

I hope you liked yesterday’s post SQL Server Key Lookup operator Part1. Today, we are going to decode query plan in text and xml then find out if this operator appears anything different than of graphical plans.

USE [AdventureWorks2012]

SET SHOWPLAN_TEXT ON

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

SET SHOWPLAN_TEXT OFF

KLTextPlan1

KLTextPlan2

Statement output image is broken into two parts to fit into the window. As can be seen, output shows clustered index seek with LOOKUP keyword at the very end and not specific mention of key lookup keyword. Now let us verify XML plans;

USE [AdventureWorks2012]

SET SHOWPLAN_XML ON

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

SET SHOWPLAN_XML OFF

KeyLookupXML

Output is very clear and self-explanatory i.e. again no specific mention of this keyword here as well. So we can see that, SQL Server Key Lookup operator shows up only in graphical plans and its representations are different in both text as well as xml plans.

Question now is, can same query statements may produce different plan operators? Watch out tomorrow’s blog post where we are going to delve on this.

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 →

One Comment on “SQL Server Key Lookup Operator – Part 2”

Leave a Reply to Sudhir Cancel reply

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