Why the differences in execution plan between nonclustered and clustered index in SQL Server?

Question

Please explain why the below differences between non clustered and clustered index. First I am running the below two select statements.

select * 
from [dbo].[index_test2]  
where id = 1 -- Nonclustered index  on id column

select * 
from [dbo].[index_test1] 
where id = 1  -- Clustered index on id column

Execution plan shows "Table scan" for the first query and "Clustered index seek (clustered)" for the second query.

Then I am running below two statements.

select id 
from [dbo].[index_test2]  
where id = 1 -- Nonclustered index  on id column

select id 
from [dbo].[index_test1] 
where id = 1  -- Clustered index on id column

Execution plan shows "Index seek (NonClustered)" for the first query and "Clustered index seek (Clustered)" for the second query.

You can see from the above two cases, when using clustered index it is going for "Index seek" but for in case of NonClustered index it shows "Table scan" (executed with *) and it shows "Index seek (NonClustered)" (executing with index applied column-id).

Can any one clarify why the NonClustered index reacting differently on both cases?

First thing to remember is that the clustered index is your table. You can only have one clustered index on a table because it is actually the table with the rows in index key order. In your first test case select * is not covered by the non-clustered index, the index only contains the id column, so a table scan is required to return all columns. In your second test case select id is covered by the non-clustered index, so it can return rows directly from the index instead of performing a table scan. — AlwaysLearning
If you do a SELECT *, a typical nonclustered index won't be able to "cover" the query, e.g. provide all columns needed to be returned from the query. If SQL Server were to use the nonclustered index to seek the value id = 1, it would then have to do a (fairly expensive) Key lookup to get the other data from the actual data page. Doing this for a whole table just is too expensive - it is "cheaper" to just scan the whole table. That's one of the main reasons why you should NOT use SELECT * everywhere - try to select ONLY those columns really needed — marc_s
@ThangarajJ . . . How large are your tables? On very small tables, a full table scan is reasonable. — Gordon Linoff

Reza Jenabi Reza Jenabi · Accepted Answer · 2019-12-24T07:56:38

A clustered index defines the order in which data is physically stored in a table but A non-clustered index doesn’t sort the physical data inside the table.In fact, a non-clustered index is stored at one place and table data is stored in another place.

If you use an Non-Clustered Index it works in Index seek (NonClustered) mode when you call it property,but If you put where in Non-Clustered Index mode but call in select more expressions that are not Cover index change mode to Table scan

Indexes with included columns provide the greatest benefit when covering the query. This means that the index includes all columns referenced by your query, as you can add columns with data types, number or size not allowed as index key columns

But in Clustered Index, since the actual sorting is done by it, you do both in Clustered index seek (clustered) mode.

Why the differences in execution plan between nonclustered and clustered index in SQL Server?

1 Answers