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!

Venkataraman R · Accepted Answer

SQL Server also follows similar approach for composite indexes, where only the columns from left are considered for index seek. Below is the official documentation of the same:
Index on (a,b,c) will be useful for below seek queries

a
a,b
a,b,c

Read more on Index Design practices on MSDN

Consider the order of the columns if the index will contain multiple
columns. The column that is used in the WHERE clause in an equal to
(=), greater than (>), less than (<), or BETWEEN search condition, or
participates in a join, should be placed first. Additional columns
should be ordered based on their level of distinctness, that is, from
the most distinct to the least distinct.
For example, if the index is defined as LastName, FirstName the index
will be useful when the search criterion is WHERE LastName = 'Smith'
or WHERE LastName = Smith AND FirstName LIKE 'J%'. However, the query
optimizer would not use the index for a query that searched only on
FirstName (WHERE FirstName = 'Jane')

Gordon Linoff · Answer

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.

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

2 Answers

Add your own answers!

Ask a Question