Sargable – the word only a geek could love!

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s