Why scan clustered index instead seek nonclustered index?

Question

There is SQL script:

CREATE TABLE users 
  ( 
     id        INT, 
     firstname VARCHAR(50), 
     surname   VARCHAR(50) 
  ); 

CREATE CLUSTERED INDEX ix_users_id 
  ON users (id); 

CREATE NONCLUSTERED INDEX ix_users_firstname 
  ON users (firstname); 

SELECT firstname, 
       surname 
FROM   users 
WHERE  firstname = 'John';

I do not understand why for the above SELECT request, Engine of SQL Server 2019 selected follows Execution Plan:

Why the Clustered Index is scanned? I thought, faster is:

1. seek Nonclustered Index;
2. move on a Clustered Index by Clustered Index Pointer, which storing in Leaf Node of Nonclustered Index;
3. and take rest surname value from there.

Answer 1

The key idea here is that your index contains (firstname,id), but not surname. So the options for this query

SELECT firstname, 
       surname 
FROM   users 
WHERE  firstname = 'John';

are

1) Scan the clustered index

2) Seek the non-clustered index, and then for every matching row in the index, Seek on the Clustered Index to find the surname. It's this "bookmark lookup" that is the most expensive part of the query, and if a reasonable percentage of your users are named 'John', it may well be cheaper just to scan the clustered index.

This is why we have indexes with included columns. You can add surname to the index to enable this query to seek on the non-clustered index, and avoid the bookmark lookup. The index would then be a "covering index" for the query. eg

CREATE NONCLUSTERED INDEX ix_users_firstname 
  ON users (firstname) 
   include (surname);