1

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:

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.
3
  • 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. Apr 19, 2020 at 13:11
  • I am not expert in database design. Now table is empty. Does Execution Plan depend on it? Apr 19, 2020 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, 2020 at 13:16

1 Answer 1

4

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); 
4
  • contains (firstname,id)? This nonclustered index also contains id? Apr 19, 2020 at 15:49
  • 1
    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. Apr 19, 2020 at 15:52
  • 1
    if a reasonable percentage. However, how it possible estimate this percentage? Which metrics engine use for it? Apr 19, 2020 at 16:08
  • 1
    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 learn.microsoft.com/en-us/sql/relational-databases/statistics/… Apr 19, 2020 at 16:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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