Sargable Predicates are usually best practice

Every SQL query writer should know that sargable predicates are generally much faster than non-sargable predicates.  Essentially a sargable predicate can be used by the relational engine to navigate an index.  For example, the predicate Where IpCountryID=@i 

is sargable as the relational engine can use the variable @i to navigate an index on IpCountryID.  Similarly
Where IpCountryID=@i-1

is also sargable as the relational engine can add 1 to @i and use that to navigate an index on IpCountryID.  On the other hand
Where IpCountryID+1=@i

is not sargable as the relational engine cannot navigate an index while there is an arithmetic expression on IPCountryID.  These equivalent sargable and non-sargable predicates and their plans can be seen above.  The sargable predicate query uses an index seek to get the particular record, while the non-sargable predicate uses the same index but it scans the entire index checking if IPCountryID+1=@i.  An example of how the sargability of predicates is important is where you are joining a table to itself where the key has an arithmetic function.  For example, getting the previous record as in the following example

SELECT t1.*,t2.value

FROM MyTable t1

LEFT JOIN MyTable t2

ON t2.id=t1.id-1

Notice how the predicate t2.id=t1.id-1 is sargable on the left side but not the right.  This means that the relational engine can navigate to t2 using an index.  If the predicate was t2.id+1=t1.it (logically equivalent) the relational engine could not navidate to t2 using an index.  

I found an interesting situation while tuning a query on a couple of nested views.  It's not easy to reproduce, and to reproduce it I need to include other joins and logic, which will make it hard for you to comprehend.  The essence of the first view was to restrict the records to the last rolling 53 weeks.  It had the following predicate

WHERE TransactionDate >= DATEADD(WW,-53,GETDATE())

This predicate is sargable, which should be a good thing.  However, there are several views on top of this base view that are asking for month date ranges. So they have predicates with hard coded date ranges (on top of the first view) ie

WHERE TransactionDate between '20160101' and '20160131'

This predicate is also sargable, which should also be a good thing.  However, the issue I uncovered was that the optimiser now had a choice of TransactionDate>=DATEADD(WW,-53,GETDATE()) and TransactionDate>='20160101' as the lower bound to the index seek.  Sometimes it was choosing TransactionDate>=DATEADD(WW,-53,GETDATE()) as the lower bound, which is much slower than TransactionDate>='20160101', since 20160101 is a higher date (and there are no transactions for 2016 yet).  The solution to these poor performing queries was to change the first view to make the sargable predicate in the base query non-sargable.  It became 

WHERE DATEADD(WW,+53,TransactionDate) >= GETDATE()

Now the optimizer has no choice, it will use '20160101' and '20160131' as seek predicates on the index and the query runs orders of magnitude faster.

Please don't takeaway the feeling that non-sargable predicates are good.  The takeaway is that it is worthwhile looking into the plans of long running queries, understanding why the optimizer chose that path and help it choose a faster path, if one exists.