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

user_6096478 0
Viewed 501

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

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!

sql-server sql
2 Answers

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.

user_11440350

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

user_6349350
Related