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