question

JohannesMaly-3740 avatar image
0 Votes"
JohannesMaly-3740 asked SeeyaXi-msft answered

SQL Server covering index

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

sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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).

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

cheong00 avatar image
0 Votes"
cheong00 answered JohannesMaly-3740 commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi.

Thanks for your fast response.

I already checked the execution plan and I saw that the covering index is used.
The size of the table is huge so a table scan should not be an option.

My question is more a fundamental one, means, does (can) the optimizer make use of an index even if not all of the key columns are provided in a search predicate?

Best Regards
Johannes

0 Votes 0 ·
SeeyaXi-msft avatar image
0 Votes"
SeeyaXi-msft answered

Hi @JohannesMaly-3740,

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

d

oes (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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.