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.