Strange looking where clause in sql server

Question

I've found fa strange looking code in T-SQL. Here is the strange looking part:

WHERE @term1 IS NULL OR (@term1 IS NOT NULL AND (businessLogicCondition))

The query works well, but what is the point to do @term1 IS NULL OR (@term1 IS NOT NULL. Maybe it is connected with the fact that sql server doesn't have short-circuits WHERE condition evaluation. From my C# background it seems really strange.

The question is: is it really a good practice to do such double comparisons? does it affect execution plan?

Added: my businessLogicCondition is misleading, so here is revised version:

WHERE @term1 IS NULL OR (@term1 IS NOT NULL AND (ua.FirstName LIKE @term1 OR ua.SecondName LIKE @term1))

from my point of view the query is equivalent to:

WHERE @term1 IS NULL OR ua.FirstName LIKE @term1 OR ua.SecondName LIKE @term1

the question is whether the original option can improve execution plan, or it depends?

Answer 1

SQL Server does NOT have guaranteed short circuiting in that scenario. The point of this construct is so that businessLogicCondition is only evaluated as part of the WHERE clause in the case where @term1 is populated. It may still be evaluated as part of query processing, because you can only control order of evaluation in a few special cases (no pun intended).

This WHERE clause would actually have different meaning, unless businessLogicCondition actually referenced @term1 explicitly and in such a way that it could only return true if @term1 is NOT NULL:

WHERE @term1 IS NULL OR (businessLogicCondition)

businessLogicCondition may still be evaluated, but this form of the WHERE clause could, potentially, return rows based on businessLogicCondition, regardless of whether @term1 is NULL or NOT NULL.

Now, with the pertinent info added to your question:

No, in this specific case, the @term1 IS NOT NULL AND is redundant and cannot possibly help the optimizer come up with a better plan (except maybe in the case where there is a filtered index with that same). In fact in the above scenario I mentioned before I had that information, it won't lead to a better plan either, but it can change whether the results are correct or not.

It is important to note that as written SQL Server will optimize and cache an execution plan based on the first execution. It may be the case that a better plan can be used in the scenario where @term1 is NOT NULL (e.g. when @term1 is something like somestring%, it may be able to seek, but not with %somestring%). So you might consider using dynamic SQL to formulate one version of the query or the other, depending on the value of the parameter, especially if you have multiple optional parameters (I call this "the kitchen sink" procedure). The OR that remains may still be problematic; there are a lot of variable factors.

I have videos about my solution to "the kitchen sink" here and here as well as a blog post about it.

Answer 2

The idea of such code is not to repeat "the same" statement twice when nullable variable passed. Another option in your case is to have 2 different statements ,

IF @term1 IS NULL 
   SELECT .... 
   -- note - no "WHERE" 
ELSE
   SELECT .... WHERE ua.FirstName LIKE @term1 OR ua.SecondName LIKE @term1

Approach with just one statement may be still valid in many cases, you just need to keep in mind that one statement by default will have just one execution plan. In your example I'd guess full table scan will be very likely used in any case , so 1 statement is good here; in other cases optimal plans can depend on passed parameters.