SQL Server Pop Quiz: A Key Lookup Without the Index

Execution Plans
13 Comments

How do we get this to show up in an execution plan:

Without having an index seek or index scan operation on the same table?

Take any copy of the Stack Overflow database and write a query that will produce a key lookup on the Users, table, but it’s not allowed to have an index seek or index scan operation on the Users table. You’re allowed to change the database structure – for example, if you need to change the clustered index, drop it, add nonclustered indexes, whatever – and of course include those changes in your comment as well.

Post your query in the comments here by end of day Wednesday, and I’ll pick my favorites and put ’em in the blog on Friday. My favorite creative answers will get 1 year access to the Recorded Class Season Pass: Masters Classes. My own answer blog post is written & scheduled for Thursday.

Previous Post
[Video] Office Hours in the Valley of Fire State Park
Next Post
Updated First Responder Kit and Consultant Toolkit for July 2022

13 Comments. Leave new

  • Michael J Swart
    July 18, 2022 3:39 pm

    Okay, this one took some out of the box thinking. Here’s what I tried:

    Add a ton of newlines to the name of the PK, this adds a lot of vertical space to each “row” of the graphical query plan. When you can only see one operator on the screen at a time, is that mission accomplished? (Not quite, No seeks/scans allowed even offscreen).

    Adding weird unicode to identifiers to take advantage of maybe some rendering quirk? Unicode is notoriously tricky. (But I couldn’t get this to work.)

    Is there some permission that will selectively hide the operators you’re not allowed to see? (Nope showplan privileges aren’t that granular)

    I recognized that key lookups are still just seeks into a table. They’re only shown as key lookups when SQL Server renders the query plan and needs to pick an object. I hacked the xml and added Lookup=”True” to an index seek. That’s enough for SQL Server to say, If you say so boss, I’ll use the “key lookup” operator.

    This logic seems to work in paste-the-plan too:
    https://www.brentozar.com/pastetheplan/?id=Syj3nxX25

    Reply
  • you have a single row key lookup showing as 54% of the plan cost. Given the single row key lookup is 0.003281, that would make the remaining portion 46%, or 0.002797.

    Reply
  • This will win no prizes for creativity, and requires Enterprise or equivalent:

    [sql]
    CREATE OR ALTER VIEW dbo.V
    WITH SCHEMABINDING
    AS
    SELECT U.Id
    FROM dbo.Users AS U
    WHERE U.AccountId = -1;
    GO
    CREATE UNIQUE CLUSTERED INDEX c ON dbo.V (Id);
    GO
    SELECT U.DisplayName
    FROM dbo.Users AS U
    WHERE U.AccountId = -1
    OPTION (KEEP PLAN);
    [/sql]

    Reply
    • Yesss! And this was similar to my blog post solution coming up this week. Nicely done!

      Reply
      • I do have an interesting RID Lookup without a same-table seek or scan.

        See the actual plan for the DELETE below:

        Reply
  • This solution is similar to Michael’s (I was too late).

    No Index Seek or Scan – Check
    Only Key lookup – Check (bonus points for the same percentage)
    Does it show up in the Execution plan? – Check. It’s the column name

    tsql
    SELECT CAST(N'

    ' AS xml) AS [Execution plan]

    Reply
  • I have the same solution as the first post … after two days with various indexes and views playing … LOL

    Reply

Leave a Reply

Your email address will not be published.

Fill out this field
Fill out this field
Please enter a valid email address.