Does a 4-column composite index benefit a 3-column query?

Question

I have a table with columns A, B, C, D, E, ..., N. with PK(A). I also have a composite, unique, non-clustered index defined for columns D, C, B, A, in that order.

If I use a query like:

where D = 'a' and C = 'b' and B = 'c'

without a clause for A, do I still get the benefits of the index?

Answer 1

Yeah, it will still use the index properly and make your query more efficient.

Think of it like a nested table of contents in a text-book:

• Skim till you see the chapter name (e.g. "Data Structures")
  • Skim until you see the relevant section title ("Binary Trees")
    • Skim until you see the relevant topic (e.g. "Heaps").

If you only want to get to the binary trees section, the contents going to the topic level doesn't hurt you at all :).

Now... if you wanted to find binary trees and you didn't know they were a data structure, then this table of contents wouldn't be very useful for you (e.g. if you were missing "D").

Answer 2

Yes, SQL server can perform a seek operation on the index (D, C, B, A) for these queries:

WHERE D = 'd'
WHERE D = 'd' AND C = 'c'
WHERE D = 'd' AND C = 'c' AND B = 'b'
WHERE D = 'd' AND C = 'c' AND B = 'b' AND A = 'a'

And it could perform a scan operation on the said index for these:

WHERE C = 'c' AND B = 'b'
WHERE A = 'a'
-- etc

But there is one more thing to consider: the order of columns inside indexes matters. It is possible to have two indexes (D, C, B, A) and (B, C, D, A) perform differently. Consider the following example:

WHERE Active = 1 AND Type = 2 AND Date = '2019-09-10'

Assuming that the data contains two distinct values for Active, 10 for Type and 1000 for Date, an index on (Date, Type, Active, Other) will be more efficient than (Active, Type, Date, Other).

You could also consider creating different variations of the said index for different queries.

PS: if column A is not used inside the WHERE clause then you can simply INCLUDE it.