SQL Server : expected clustered index scan, but got non clustered index scan

0
votes

I have this table:

CREATE TABLE Ta
(
    coda int NOT NULL PRIMARY KEY,
    a2 int UNIQUE   
);

and a SQL select statement:

select *
from Ta

I have a clustered index, the primary key and a non-clustered index, specified by the unique constraint.

Executing the select I get the following execution plan:

enter image description here

But I'm not sure why.

The data should be on the leaf level, therefore it should scan the leaf level, hence it should do a clustered scan.

EDIT: the table has 10000 rows, coda has values from 9999 to 0 and a2 has values from 0 to 9999.

enter image description here

1
sql-serverindexing
How large is the table? On small tables, the difference may be immaterial. – Gordon Linoff
10000 rows, also edited the question, thanks – Ghimpu Lucian Eduard
They will be basically the same, both indexes cover the query. The CI key is included in all NCIs. Both indexes will have roughly the same number of pages so there is no difference. – Martin Smith

1 Answers

2
votes

The non-clustered index is a covering index for the query. That is, the index contains all of the columns needed to satisfy the query.

The execution plan is showing that SQL Server is using the non-clustered index.

For the given query, it seems like a reasonable execution plan.

If there were some predicate (a WHERE clause condition on a column) or an ORDER BY clause, then we would expect that to influence which index is used.

But in this case, retrieving two columns (a2 and coda) for every row in the table with the rows returned in an unspecified order, then a full scan of either index is a suitable plan.