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

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

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