0

I'm trying to learn about covering indexes. In the Northwind database, I select from the table Categories:

enter image description here

As you can see the table has a non-clustered index on the column CategoryName.

This SQL query:

select CategoryName  from Categories where Categories.CategoryName like 'Beverages'

returns an execution plan with an index seek:

enter image description here

However, this:

select CategoryName ,Description from Categories where Categories.CategoryName like 'Beverages'

returns this execution plan with an index scan using the primary key index, which isn't expected:

enter image description here

I can find the expected behaviour only when I force the query with the non-clustered index:

 select CategoryName ,Description from Categories
    with(index(CategoryName))
 where Categories.CategoryName like 'Beverages'

enter image description here

What is the problem?

1
  • 2
    FYI - Northwind is a very obsolete sample database. It was designed for sql server 2000. More recent samples are here
    – SMor
    Dec 20, 2020 at 14:53

2 Answers 2

3

There is no Description column in your index but there is in your query. So, SQL Server has to get this column. There are two options for doing that:

  • non-clustered index seek + key lookup
  • clustered index scan

If your statistics make SQL Server think it will read a lot of data, SQL Server makes a choice between the non-clustered index seek + key lookup or clustered index scan, this called tipping point.

Because, if SQL Server has to read huge data, the clustered index scan can be more efficient rather than non-clustered index seek + key lookup.

There is a good blog post here.

0

It's because the Nonclustered Index doesn't include Description so it doesn't cover the second query.

The only two options then are either use that Nonclustered Index and then do a Key Lookup on the Primary Key (effectively the Clustered Index in this case) to get the Description field (two operations) or just use the Clustered Index from the beginning to find and filter down the data, since the Clustered Index includes all fields (one operation).

It chooses one operation over the other depending on the difference in Operator Costs in the execution plan which will vary from query to query and dataset to dataset, depending on thing likes the Cardinality of the data.

This is known as The Tipping Point.

If you changed the Nonclustered Index to include the Description field then it should use the Nonclustered Index instead, in one operation (no Key Lookup even needed).

E.g:

DROP INDEX CategoryName ON Categories
CREATE INDEX CategoryName ON Categories (CategoryName) INCLUDE (Description)

Your Answer

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

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