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?