Colleague says to never use an OR statement in SQL, is this true?
Never listen to anyone saying that you should never do X.
Generally, you should not try to outsmart the query optimizer without some really good reasons.
It is true that in certain scenarios too many ORs can lead to a suboptimal1 plan, but you should consider every such scenario individually and only look for workarounds if the original query performance is unacceptable .
If you do need to address performance of the query you posted, please consider asking a different question.
1 - From the human point of view. The plan in fact will be optimal for that particular query variant (to the extent of the optimizer capabilities); what I mean is that rewriting the query might produce a different plan that executes faster or consumes fewer resources.
It's not that you should never use OR in a where clause, it's that the particular pattern you're following in this case is a very bad one. I've written and recorded on the subject:
- Optional Parameters and Missing Index Requests
- The SQL Server Performance Tasting Menu: Optional Parameters
Additionally, the pattern you chose to replace it with will not work out the way you think. See here:
- SQL Server--If logic in stored procedure and the plan cache
- The Not Very Mighty IF Branch
Joins with or clauses can also be difficult:
- Joins With OR Clauses =
Under different circumstances, e.g. not using optional parameters, OR can be used to little detriment, as long as you have indexing to support the predicates. I'm not saying it's always best, but it's workable.
There are many times you'll find yourself in a better position by using UNION ALL to replace OR, but in the specific case you have, you're better off using dynamic SQL to build the appropriate query and execute it. That technique is covered in the video I linked to.
Picking up from comments on Erik's answer:
You basically have the ol' optional parameters case. It seems you expect us to tell you which option is best. We can't do that since we aren't in your shoes. What we can do is to outline a few alternatives for you to investigate and then determine which is better your your particular situation.
Branching with IF and hand-crafting each query shape. As long as you are aware that parameters passed to a proc will be used when the proc-plan is generated for all queries, regardless of your branching code. I.e., you end up with for instance the second query at run time when plan is generated, but plans are generated for all queries. Chances are that the other plans now will have out-of-whack selectivity estimates. Just imagine the proc without all the branching logic. This is what the optimizer will see. You might end up with the "sometimes it is fast, sometimes it is slow" situation. Options here for you are OPTIMIZE FOR to get "plan stability" and OPTION(RECOMPILE), although the last one might not be interesting if you keep reading.
Using your query shape with OR and throwing OPTION(RECOMPILE) to allow for index usage. But are you prepared to pay for plan generation on each execution? That is for you to answer.
Using dynamic SQL with sp_executesql so you end up with generated SQL matching your various query shapes. You now have possibility of index usage and parameter sniffing the "real" values. But dynamic SQL has its drawback. To be licensed to use dynamic SQL, one should have read Erland's article on the topic first. :-)
So, as it often is, there are upsides and downsides with all alternatives. Erland's article has a section on optional search arguments. Reading that article is a good start, hopefully allowing you to decide which alternative(s) is best for your particular situation.