Question: So here is the original query I'm working with

```
SELECT TOP(10) * 
FROM Orders o
WHERE (o.DateAdded >= DATEADD(DAY, - 30, getutcdate())) AND (o.DateAdded <= GETUTCDATE())
ORDER BY o.DateAdded ASC,
o.Price ASC
o.Quantity DESC

```

Datatype:

* DateAdded - smalldatetime
* Price - decimal(19,8)
* Quantity - int

I have an index on the Orders table with the same 3 columns in the same order, so when I run this, it's perfect. Time < 0ms, Live Query Statistics shows it only reads the 10 rows. Awesome.

However, as soon as I add this line to the WHERE clause

```
AND o.Price BETWEEN convert(decimal(19,8), 0) AND @BuyPrice 

```

It all goes to hell (and unfortunately I need that line). It also behaves the same if it's just o.Price <= @BuyPrice. Live Query Statistics shows the number of rows read is ~30k. It also shows that the o.Price comparison isn't being used as a seek predicate, and I'm having a hard time understanding why it isn't. I've verified @BuyPrice is the right datatype, as I found several articles that discuss issues with implicit conversions. At first I thought it was because I had two ranges: first the dateAdded then Price, but I have other queries doing with multi column indexes and multiple ranges and they all perform just fine. I'm absolutely baffled as to why this one has decided to be a burden. I've tried changing the order of columns in the index, changing them from ASC to DESC, but nada.

Would highly appreciate anyone telling me what I'm missing. Thanks

Answer: 
It is impossible for the optimizer to use two range predicates at the same time.

Think about it: It starts scanning from a certain spot in the index sorted by `DateAdded`. It now needs, within each individual `DateAdded` value to seek to a particular `Price`, start scanning, and stop at **another** `Price`, then jump to the next `DateAdded`.

This is called skip-scanning, it is only efficient when the first predicate is not very many values, otherwise it is inefficient, and because of this, only Oracle has implemented it, not SQL Server.
I think this is due to the `TOP 10` which cannot take place before the `ORDER BY`.
And this `ORDER BY` must wait until the result set is ready.

Without your additional price range, the `TOP 10` can be taken from the existing index directly. But adding the second range will force another operation to be run first.

In short:

* First your filter must get the rows for the price range together with the date range.
* The resulting set is sorted and the top 10 rows are taken.

Did you try to add a separate index on your price column? This should speed up the first filter.

We cannot predict the execution plan in many cases, but you might try to

* write an intermediate set, filtered by the date range, into a temp table and proceed from there. You might even create an index on the price column there (Depends on the expected row count. Probably the best option).
* use a CTE to define a set filtered by the the date range and use this set to apply your price range. But a CTE is not the same as a temp table. The final execution plan might be the same as before...
* use two CTEs to define two sets (one per range) and use `INNER JOIN` as a way to get the same as with `WHERE condition1 AND condition2`.