CODEWITHSUNDEEP
sql-serverdatabaseindexingclustered-indexnon-clustered-index
Mucida
Mucida

Reputation: 603

SQL Server: Key Lookup without Clustered Index

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?

Upvotes: 1

Views: 822

Answers (2)

Martin Smith
Martin Smith

Reputation: 452988

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.

Upvotes: 2

Pரதீப்
Pரதீப்

Reputation: 93694

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

Upvotes: 1

Related Questions