Does the "leftmost prefix rule" of Index applies to SQL Server?

Question

In MySQL, the leftmost prefix is applicable in the case of multi-column Index, mentioned in

• https://dev.mysql.com/doc/refman/8.0/en/mysql-indexes.html
• https://dev.mysql.com/doc/refman/8.0/en/multiple-column-indexes.html

---

Say an Index is made of (col1, col2, col3). Combinations Eligible for Index Search:

• (col1)
• (col1, col2)
• (col1, col2, col3)

Combinations Not Eligible for Index Search:

• (col2)
• (col2, col3)
• (col3)

---

Does SQL Server uses the same rule for Index Seek mechanisms too? Is there an official document that I can refer to? I have tried searching it around but no luck :( Thanks in advance everyone!

Answer 1

That rule is basically the rule for scanning b-tree indexes.

That said, there is an alternative scan method, which Oracle calls "skip scan". This allows scanning an index on keys that are not the left-most in the index. Some databases implement this, to a greater to lesser extent. Here is a good introduction to the topic. I don't believe that SQL Server implements skip-scans.

Although skip-scans are powerful, their utility depends on the cardinality of the first key in the index. If the first key has high cardinality, then they are not very effective.