Introducing Key Lookups, finding them in execution plans, and resolving them

Key Lookup is a bookmark lookup on a table with a clustered index. Key Lookup is used by SQL Server while retrieving information regarding non-key column. All the queries that use non-clustered index wouldn't have Key Lookup but all Key Lookup occurrences are accompanied by a non-clustered index. One more thing to remember is that Key Lookup always enjoys the company of Nested Loop operator.

Getting ready

We are going to create a table to see different effects of Key Lookup operator in execution plan. In order to generate the case of Key Lookup, we need two essential things to be present on the table:

• Clustered index

• Non-clustered index

When you have predicate based on key field of non-clustered index, which meets seek on the same index and goes to clustered index to retrieve the data for non-key field of non-clustered index, it generates Key Lookup, which we will achieve by creating SalesOrdDetailDemo table...