Sargable!! A word that the SQL geeks came up with, which fortunately has not been accepted into the common English lexicon.
Say it out loud a few times. Doesn’t it sound like “Searchable”? (If you said no, then try it with German accent, or Polish accent, or Indian accent or something, until it sounds like “searchable).
So now that you know sargable is geekspeak for searchable, let us figure out what the geeks really meant. We will do this with an example.
Suppose you have a table that has a datetime column. Let us pick Orders table – everyone else seem to pick that, so why shouldn’t we? In that table you have an order date column which includes a timestamp. There are other columns such as orderid, customerid etc. as well
CustomerId OrderId OrderDate 1234 10 2013-07-12 12:48:11.400 1234 11 2013-07-15 11:30:21.150 1234 12 2013-07-15 13:32:41.000
Now let us assume also that there is an index on the OrderDate. A non-clustered non-unique index. (Just bear with me, will ya? I need all this to make my story work).
So there you are sitting minding your own business, and then the pointy-haired guy comes along. Or it might be the pointy-haired girl (I may be shallow, but I am not a sexist. I want to give equal opportunity to pointy haired girl bosses as well). He/she/it wants you to get all the orders from yesterday, July 12th.
So now the question is how do you do this? There are couple of different – or may be even more – ways you can write the query. I want to take the three methods below and consider which one might be better for us (or rather for SQL Server).
-- 1 SELECT CustomerId, OrderId, OrderDate FROM ThatOrderTable WHERE CAST(OrderDate AS DATE) = '20130712'; -- 2 SELECT CustomerId, OrderId, OrderDate FROM ThatOrderTable WHERE DATEADD(dd,DATEDIFF(dd,0,OrderDate),0) = '20130712'; -- 3 SELECT CustomerId, OrderId, OrderDate FROM ThatOrderTable WHERE OrderDate >= '20130712' AND OrderDate < '20130713';
Which of these would SQL Server like better?
I won’t keep you in suspense; you know the answer anyway. It is #3. The reason is that unlike #1 and #2, the OrderDate is not within any function. So SQL Server is able to use the index to filter on the OrderDate column. Think about it. If OrderDate is inside a function, there is no way SQL Server is going to know what is going to come out of the function, so it can’t relate the output of the function to what is in the index. Ergo, it can’t use the index.
That is all there is to the big word Sargable. So next time some SQL expert wannabee tries to scare you with “oh, we should make that sargable”, you can be cool and pretend that you are the Jedi master of sargability.
By the way, they (they being Wikipedia) say that Sargable means “Search ARGument ABLE”. Now that I think about it, don’t read what I wrote. Just go to Wiki http://en.wikipedia.org/wiki/Sargable. They have a better explanation. Mine is rather shallow compared to their explanation.