Colleague says to never use an OR statement in SQL, is this true?

My colleague now responsible for SQL development says that I should never use an OR statement because it will mess up the query optimizer and ignore table indexes producing slow queries. I couldn't find any example of this while googling. The alternative to the following query becomes really ugly with a dozen or so blocks of code which look almost identical(to the example) using if else statements for each variable state. Note the variables that are checked to short-circuit and return all results if the value is 2 or else filter by the field.

I asked for some resources containing these claims about why not to use an OR statement and received the following links (we are using MS SQL Server).

• https://stackoverflow.com/questions/5639710/union-all-vs-or-condition-in-sql-server-query

• https://bertwagner.com/2018/02/20/or-vs-union-all-is-one-better-for-performance/

• http://sqlserverplanet.com/optimization/using-union-instead-of-or

None of these examples seem to resemble the current implementation as below. I find it hard to believe that this code is problematic but please let me know if it is. I would also like some more information where the comment made about not using OR might actually hold true and why so, as to better understand the issue.

SELECT
    e.EmployeeName,
    e.DepartmentName,
    crs.Title,
    c.Name as CompanyName
FROM Employee E
Left Outer Join Company c ON c.Id = @companyId
    INNER JOIN Department d on e.DepartmentId = d.Id 
WHERE   
    c.Id = @companyId           
    AND (@Active = 2 OR  crs.IsActive = @Active)
    AND (@Dot = 2 OR IsDot = @Dot)
    AND crs.CompanyId = @companyId
    AND d.CompanyId = @companyId
ORDER BY EmployeeName, Title, PassedDate

I am a believer that duplicating code is always bad unless there is a really good reason. After testing the query I confirmed that the proper indexes were being used. After mentioning this, I was told that he would use best practices. I haven't seen any best practices telli

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.

StackExchange Database Administrators Q#278156, answer score: 25