Self join to eliminate Key LookUp.

A Key Lookup retrieves values from the clustered index using the primary key.

It occurs when a non-clustered index is used for seeking, but some required columns (e.g., in SELECT or ORDER BY) are missing from the index.

If many rows are returned, Key Lookups can slow down a query. Adding included columns to the index can prevent this, but I'll show an alternative solution below.

I will use the StackOverflow2013 database.

Existing Non clustered index.

DROP INDEX IF EXISTS Posts.IX_Posts_MUSQL_01;

CREATE INDEX IX_Posts_MUSQL_01 ON Posts(PostTypeId, OwnerUserid, Score);Query with Lookup.        

Query that will use a Key Lookup to get the needed columns (p.*).

/*Clean up any cached execution plans*/
DBCC DROPCLEANBUFFERS;

DECLARE
    @PostTypeId BIGINT = 1;

SELECT TOP (500)
    u.DisplayName
    , p.*
FROM 
    dbo.Posts p
JOIN
    dbo.Users u
        ON u.Id = p.OwnerUserId
WHERE
    p.Score >= 2
    AND p.PostTypeId = @PostTypeId;        

Execution plan (total execution time 3 sec ish). The Nested Loops join must run a Key lookup for each row.

Query with self join.

/*Clean up any cached execution plans*/
DBCC DROPCLEANBUFFERS;

DECLARE
    @PostTypeId BIGINT = 1;

SELECT TOP (500)
    u.DisplayName
    , p2.*
FROM 
    dbo.Posts p
INNER JOIN
    Posts p2
        ON p2.Id = p.Id
JOIN
    dbo.Users u
        ON u.Id = p.OwnerUserId
WHERE
    p.Score >= 2
    AND p.PostTypeId = 1;        

Execution plan (total execution time 0.2 sec ish). The Nested Loops join can seek all needed columns in the clustered index.