Conor vs. Index SARGability with complex scalar expressions

  • Article

In my prior post, I introduced the concept of runtime constants.  Now let’s take this and apply the concept further to the topic of matching scalar predicates to indexes.

 

I will paraphrase a customer question I was mailed after SQL Bits last week:

If I have a predicate like this:

WHERE (SomeDateField >= DATEADD(DAY,-1,GETDATE()))

Is this a poor programming practice?  Sometimes people say to “avoid functions in the WHERE clause” and instead use parameters:

WHERE SomeDateField >= @p

 

Answer:  I have no problem with this first (non-parameterized) formulation.  The general concern with scalar logic in the WHERE clause is if it blocks index matching.  In this case it will not because the scalar logic is entirely on the non-column side.  As long as the index column you are matching (SomeDateField in this case) is not in a larger scalar expression, you are generally going to be fine. 

Now, you can use parameters if you desire from a style standpoint, but I would personally use parameters because I need parameters and not for stylistic reasons.  I’ve blogged in the past about parameter sniffing and how this can be tricky.  You might not get the index seek if you move to the parameterized form.  Usually I advise people to give the optimizer the best chance to help get you a plan that will make you happy :).

 

So, to take the question one step further, consider the following two cases:

 

WHERE col_with_idx + 1 = something

WHERE col_with_idx = something – 1

You want to use the SECOND pattern – SQL Server will not do general scalar arithmetic to try to convert the first form into the second form so it becomes SARGable.  THIS is the pattern I would recommend you follow very carefully.

 

Final note – in my prior post, I mentioned that some expressions are “runtime constants”.  These can be pulled out and executed once instead of once per row.  So, this might mitigate the concern over having functions in the “other” side of the scalar expression in your WHERE clause.

 

Happy Querying!

 

Conor