There is SQL script:

     id        INT, 
     firstname VARCHAR(50), 
     surname   VARCHAR(50) 

  ON users (id); 

  ON users (firstname); 

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

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

enter image description here

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.
  • More information will be needed. If it's a small table, it may be faster to do a scan. Upload the execution plane to pastetheplan. – Tony Hinkle Apr 19 '20 at 13:11
  • I am not expert in database design. Now table is empty. Does Execution Plan depend on it? – Adam Shakhabov Apr 19 '20 at 13:14
  • 3
    @AdamShakhabov, the cost-based optimizer uses estimated row counts to determine the optimal plan. Insert a couple of rows and update statistics and you'll probably see the expected index seek. – Dan Guzman Apr 19 '20 at 13:16

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

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


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

  ON users (firstname) 
   include (surname); 
  • contains (firstname,id)? This nonclustered index also contains id? – Adam Shakhabov Apr 19 '20 at 15:49
  • Yep. id is the clustered index key, so it's included in every nonclustered index as the "row locator". Otherwise you wouldn't be able to distinguish among the various Johns. – David Browne - Microsoft Apr 19 '20 at 15:52
  • if a reasonable percentage. However, how it possible estimate this percentage? Which metrics engine use for it? – Adam Shakhabov Apr 19 '20 at 16:08
  • SQL Server tracks statistics on the leading column of each index, and will have a histogram tracking the frequency of each distinct value. So a query for 'John' might get an clustered index scan, while one for 'Archibald' uses a non-clustered index seek + bookmark lookup. See docs.microsoft.com/en-us/sql/relational-databases/statistics/… – David Browne - Microsoft Apr 19 '20 at 16:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.