1

I have a table named Scan that has just two columns: id (int) and a (char).

It starts without any index. So I created a nonclustered index link following

CREATE INDEX ix_id ON scan(id ASC)

So I ran this select:

SELECT id, a
FROM Scan
WHERE id = 1

and this is the execution plan:

enter image description here

Why did I get a Key Lookup (clustered) if my table doesn't have any clustered index?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mucida
  • 603
  • 9
  • 24
  • 2
    Is `id` marked as primary key in your table? If so, SQL Server *automatically* created a clustered index on that column - this is documented default behavior. – marc_s Jan 18 '18 at 19:13
  • 2
    You can't get a key lookup on a clustered index unless you have a clustered index. So either you have a CI or maybe a bug in whatever application is showing you the plan. Those aren't the SSMS icons. – Martin Smith Jan 18 '18 at 19:14
  • @marc_s, I dont have any primary key. I created the test database, then just a simple table without keys or constraints. Then I manually added the snon-clustered index. – Mucida Jan 18 '18 at 19:38
  • Where are you getting this graphical plan? – Martin Smith Jan 18 '18 at 19:39
  • @MartinSmith I am using SQL Operations Studio and Docker as server. AS I mentioned above, I have no clustered index. i don't know where this key lookup came from – Mucida Jan 18 '18 at 19:40
  • 2
    Maybe SQL Operations Studio is showing the wrong icon then. – Martin Smith Jan 18 '18 at 19:41
  • 1
    found this https://github.com/Microsoft/sqlopsstudio/issues/391, an issue from sql operations studio saying that key lookup are being presented instead of RID lookup. They closed it, I but I have the latest version, and the problem still there. But you are right, is a bug – Mucida Jan 18 '18 at 19:53

2 Answers2

2

Why did I get a Key Lookup (clustered) if my table doesn't have any clustered index?

You didn't. This is presumably a bug in the html-query-plan library used by SQL Operations Studio.

The same issue is also visible on the Paste The Plan site (example).

As you know (because you found it!) the bug report is here.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • Thnak you!Now I'm trying to understand why they closed the issue but the problem still there on the latest version, lol – Mucida Jan 18 '18 at 20:03
  • 1
    The open source project looks like it has a relevant commit 23 days ago (Refactor logic for Key Lookup and RID Lookup). https://github.com/JustinPealing/html-query-plan/commit/7721a0f68ff51fc4bf599846c91a65488b0896b6 Maybe it just hasn't made its way to the shipped product yet. – Martin Smith Jan 18 '18 at 20:05
1

Leaf nodes of non clustered index contains only the key columns, so anything apart from key column is selected(in your case it is a), then it needs to perform a Rid/key lookup to pull the data from heap/clustered index

use covering index to avoid key lookup

CREATE INDEX ix_id ON scan(id ASC) include (a)

by this way column a will also be stored in your index along with key column, so the key lookup will be avoided

Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
  • 2
    Actually, if the table *were* a heap, the operation in the execution plan would be called "RID lookup" - not "Key Lookup (clustered)" – marc_s Jan 18 '18 at 19:14
  • 2
    But with a table with only two columns, adding a *covering index* to it seems a bit ..... superfluous - don't you think? Agreed - in order cases, that totally makes sense - but here?? – marc_s Jan 18 '18 at 19:18
  • 1
    @marc_s I guess OP was trying to show the problem in simplified manner. But if it is just two columns then yeah – Pரதீப் Jan 18 '18 at 19:22
  • 1
    True, I was trying yo simplify the problem. But I don't have any kind of clustered index, shouldn't key Lookup be called only on a clustered index? – Mucida Jan 18 '18 at 19:41