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:
- seek Nonclustered Index;
- move on a Clustered Index by Clustered Index Pointer, which storing in Leaf Node of Nonclustered Index;
- and take rest
surname
value from there.