This morning I was having some issues with a query using ABS() and I went straight to the lazyweb (Twitter) to ask Brent Ozar if ABS() was SARGable. Rather than tell me, he suggested I run some experiments and then blog about it. This required me to restart WordPress on this domain where it hasn’t run properly in 6 or 7 years.
I recently also created a new home test environment in VMware Fusion on my Mac Pro which is a certified antique, but will soon have 15 GB of RAM. The first thing I put into SQL 2012 was a copy of the most recent dump of dba.stackexchange.com because that is way more interesting than AdventureWorks. I created a simple index on the Users table to demonstrate below how ABS() will prevent the Index Seek for a User with a Reputation of 10,000. Both Actual Execution plans are shown below:
So, in summary, no ABS() is not SARGable, and there is plenty more to learn about SARGability from Rob Farley. This video from SQLBits is especially nice.