6

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?

  • 3
    The order or conditions in WHERE is irrelevant. The order of columns in your indexes is what matters. (IndexedColumn, OtherIndexedColumn) is different than (OtherIndexedColumn, IndexedColumn) and different than (OtherIndexedColumn, IndexedColumn, AnotherIndexedColumn) and ... – ypercubeᵀᴹ Feb 20 '15 at 17:37
  • 3
    Order here doesn't matter, but it's useful to remember that in most cases, SQL Server chooses only one index to use. It typically guesses at the most effective index and then will apply the other filters as it processes them. It's the difference between "seek predicate" and "predicate" if you want to google those. – Michael J Swart Feb 20 '15 at 17:58
  • You can answer this for yourself by comparing the execution plans. If you'd like to post them, we can clarify for you how each plan is using specific indices. – Jon of All Trades Feb 20 '15 at 18:13
13

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.