CODEWITHSUNDEEP
sql-serverselectsql-execution-plan
Dexion
Dexion

Reputation: 1101

Unused column in MS SQL execution plan

I have a query and checked the execution plan in SQL Management Studio. Some non-clustered index scan steps return the PK column of the table instead of the indexed and joined column. Example:

select a.c10, b.c20
from a inner join b on a.c11 = b.c21
where a.c12 = 23

index on table a:

create unique nonclustered index ix_a_1 on a (a.c12 asc) include ( a.c13, a.c14)

the query plan shows:

index seek, nonclustered, ix_a_1 , output list: a.primary_key_col

The column a.primary_key_col is not used in the query. Why is this the only column included in the output list?

Upvotes: 2

Views: 128

Answers (1)

gbn
gbn

Reputation: 432471

The PK column is needed to look into the clustered index (assumed PK) to get columns c10 and c11. This is known as a "key lookup"

You can remove this by making or changing the nonclustered index so it is "covering"

Try this

create nonclustered index ix_a_gbn on a (c12, c11) include (c10, c13, c14)

Some background reading from Simple Talke via Google

Upvotes: 6

Related Questions