Introduction

Hey there, SQL Server fans! In this article, we’ll address a recurring question: under what circumstances, if any, is it acceptable to create a non-clustered index where the leading column of the index key is searched using an inequality operator, and the subsequent column is searched using an equality operator?

The answer might surprise you! In this article, we’ll dive into when this indexing approach can actually be beneficial and walk through some concrete examples. By the end, you’ll have a solid understanding of this nuanced indexing scenario. Let’s jump in!

The Conventional Wisdom

Now, the general rule of thumb with SQL Server indexes is that equality columns should come first in the key order, followed by inequality columns. And in most cases, this is absolutely the right approach. After all, the query optimizer can do an equality seek when the leading column is being searched with an equals predicate. That’s usually the most efficient way to use an index.

So if you have an index on (LastName, FirstName) and your query has a WHERE clause like:

WHERE LastName = 'Smith' AND FirstName = 'John'

Then SQL Server can zip right to the rows for John Smith via an index seek. Pretty slick!

But if you flip the column order and have an index on (FirstName, LastName), that same query would have to do a less efficient index scan to find all the Johns, then filter them down to just the John Smiths. No bueno.

The Exception to the Rule

However, there are some cases where leading with an inequality column in a non-clustered index can actually be a smart move. The key is that the inequality column needs to have very high selectivity – meaning it filters out a huge portion of rows.

Imagine you have a Sales table with columns for SalesDate and SalesAmount. You frequently run queries looking for sales on a particular date that are over a certain amount, like:

SELECT * 
FROM Sales
WHERE SalesDate = '2023-03-01'
  AND SalesAmount > 1000

In this case, indexing (SalesAmount, SalesDate) could be quite effective. Why? Because presumably a small fraction of sales are over $1000. So SQL Server can seek into the index to get to the first row with SalesAmount > 1000, then scan from there, checking SalesDate = ‘2023-03-01’ as it goes.

In most databases, the SalesDate=X clause will be far less selective than the SalesAmount>Y clause. So by putting SalesAmount first, even though it’s an inequality, we’ve maximized the selectivity of the index’s leading column. This can radically reduce the number of rows SQL Server has to scan through.

Analyzing Index Usage

To figure out if you have a good candidate for a non-clustered index led by an inequality column, look at the execution plan for queries using that inequality search. If you see a high number of rows being scanned in an existing index, that’s a sign that the leading column isn’t selective enough.

Let’s look at an example. Say we start with an index of (SalesDate, SalesAmount) and run:

SELECT * 
FROM Sales
WHERE SalesDate = '2023-03-01'
  AND SalesAmount > 1000

If the execution plan shows 100,000 rows being scanned in the index to find the matching rows, that tells us there are a lot of rows for that SalesDate. SQL Server has to scan through all of them to test SalesAmount > 1000 for each one.

But if we flip the index key order to (SalesAmount, SalesDate) and see that now only 500 rows are scanned, we know we’ve hit the jackpot! By putting the much more selective SalesAmount column first, even though it’s an inequality search, we’ve drastically reduced the work SQL Server has to do.

Conclusion

So in summary, while it’s usually best to put equality search columns first in a non-clustered index, there are times when an inequality column actually belongs in the lead. The key is to look for cases where the inequality column has very high selectivity compared to the other columns in the index. With a little analysis and testing, you can build some terrifically efficient indexes using this technique!

The next time you’re indexing a big table with a highly selective inequality search column, give this approach a try. And don’t be afraid to experiment and measure the results. With practice, you’ll develop a keen sense of when this type of index can give your queries a big performance boost.

Happy indexing!

Microsoft documentation on this topic: SQL Server Index Architecture and Design Guide