Order In WHERE Clause Matter For SARGability?

Question

Suppose that I have a table with 1000 records and I have the below query, which would return 7 total records:

SELECT *
FROM MyTable
WHERE IndexedColumn > 5000
     AND OtherIndexedColumn = 2

Since the two columns have up to date indexes, SQL Server can make assumptions and find the values faster and both queries are SARGable (ideally fewer reads). However, suppose that I need to make sure that the value is not equal to another value, let's say 12 for a different column, so I would have to add

AND AnotherIndexedColumn <> 12

If that's the last statement in the WHERE clause, does SQL Server use the SARGability in the first two WHERE statements to first filter, get the 7 rows, then look to see if each row of the 7 are not equal to 12, or does it apply the <> to every row in the original data set of 1000 rows?

The reason that I'm asking is because I'm aware that I could use a subquery or CTE to do the first part of the SARGable filter, then of the 7 rows, look and see if each is not equal, but is the query optimizer already doing that behind the scenes, or is it best to do it myself?

Answer 1

In theory, and particularly in modern versions of SQL Server, the order of the WHERE clauses makes absolutely no difference. SQL Server will generally process the filters in the most efficient order, and will pick the index that it determines will allow for the most efficient query. A difference that may occur is when the WHERE clause includes JOIN criteria or applies filters to outer joined columns; other differences can come into play when options like FORCE ORDER or specific trace flags are in use.

Re-writing as a CTE to "force" SQL Server to process a filter inside the CTE first doesn't work - SQL Server is going to re-write it anyway, and process it in the order that it deems best. This is why, for example, you can't escape invalid conversion errors by using a CTE or a subquery to first filter out the bad data - SQL Server might still present the bad data to the conversion before any filter does anything.

(Erland complained about this in Connect #537419.)

So, again generally, don't re-write your SQL code to try to out-smart the optimizer or prevent it from doing something stupid. There are occasions where it can pick the wrong index that might be resolved by changing the query itself, but deal with those in situations where you already believe that the optimizer has picked the wrong index (and before solving those, eliminate more likely causes like parameter sniffing, bad statistics, etc.).