SQL Server covering index

Question

I'm currently trying to optimize a number of indexes of an SQL Server OLTP database. I would like to shrink the number of indexes by either deleting them because they are of no use or combining those which have overlapping keys and or includes.

One thing I'm not fully sure of is how a nonclustered index with multiple key columns is used if only a part of the index key is provided in a search predicate.

Assume an index key like (IDCustomer, OrderDate) and an included column (OrderNumber).

When a query like

SELECT IDCustomer, OrderDate, OrderNumber FROM table WHERE IDCustomer = 123

gets fired ... is the index used event though there are not all of the key columns provided in the serach predicate or should I create a separate index for the IDCustomer column?

And what if an order gets applied like

SELECT IDCustomer, OrderDate, OrderNumber FROM table WHERE IDCustomer = 123 ORDER BY OrderDate

Can I benefit from that index?

Best Regards
Johannes

Answer 1

A good way to understand whether an index is useful is that you imagine that you have the data written down on paper or a plain text file in the order given by the index, and then think of how you as a human would find data in that text.

So if you have an index (IDCustomer, OrderDate) INCLUDE OrderNumber and you have the query:

   SELECT IDCustomer, OrderDate, OrderNumber FROM table WHERE IDCustomer = 123  

That index is perfectly usable. And if you slap on ORDER BY OrderDate, the index is even better.

But if you try the query

   SELECT IDCustomer, OrderDate, OrderNumber FROM table WHERE OrderDate = '20210707'  

SQL Server can no longer seek the index, since the rows with an OrderDate of July 7th are scattered all over the index. But since the index is covering, SQL Server will scan the index, but that will be from start to end.

More generally, say that you have an index on (a, b, c, d, e), and a query that goes:

   SELECT ...  
   FROM   tbl  
   WHERE  a = @val1  
      AND  b = @val2  
      AND  d = @val3  
      AND  e = @val4  

SQL Server might use the index for this query, but it can only use (a, b) as seek predicates since c is absent in the WHERE clause. If you were to deal with this as a human, you may know that c can only be 0 or 1, so you would just read in two places of the list, but SQL Server does not have any rule for this, but resorts to scanning the entire range for (a,b).

Answer 2

Hi @Johannes Maly ,

SELECT IDCustomer, OrderDate, OrderNumber FROM table WHERE IDCustomer = 123 ORDER BY OrderDate

The execution sequence is as follows:
FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY

does (can) the optimizer make use of an index even if not all of the key columns are provided in a search predicate

However, you can check the execution plan to help you.

Best regards,
Seeya

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

You can put your query to run in the Management Studio and run it with "Show Actual Execution Plan" button enabled and see if it will use the index.

It's hard to tell just by looking at the SQL because, if for some reason like "the table size is small", SQL server may think it's more efficient to do table scan instead.