Make your queries SARGable

Here's a pop quiz, and if you pass it, you can probably skip this article:

Which of the following three predicates is 'Best':

                    Copy
                    
                 
SELECT * FROM Orders o WHERE DateDiff(DAY, o.OrderDate, GetDate()) >= 7  -- A
SELECT * FROM Orders o WHERE DateAdd(DAY, o.OrderDate, 7) < GetDate()    -- B
SELECT * FROM Orders o WHERE o.OrderDate < DateAdd(DAY, -7, GetDate())   -- C

Give it a minute - hover to reveal the answer

Option C - Because o.OrderDate is not inside of a function and thus SARGable

The answer, dear reader,

What makes a SQL statement sargable?

                    Copy
                    
                 
Bad: Select ... WHERE isNull(FullName,'Ed Jones') = 'Ed Jones'
Fixed: Select ... WHERE ((FullName = 'Ed Jones') OR (FullName IS NULL))

Bad: Select ... WHERE SUBSTRING(DealerName,4) = 'Ford'
Fixed: Select ... WHERE DealerName Like 'Ford%'

Bad: Select ... WHERE DateDiff(mm,OrderDate,GetDate()) >= 30
Fixed: Select ... WHERE OrderDate < DateAdd(mm,-30,GetDate()) 

What does the word “SARGable” really mean?

Sargable operators: =, >, <, >=, <=, BETWEEN, LIKE, IS [NOT] NULL, EXISTS
Sargable operators that rarely improve performance: <>, IN, OR, NOT IN, NOT EXISTS, NOT LIKE

If You Can’t Index It, It’s Probably Not SARGable

How to Search and Destroy Non-SARGable Queries on Your Server