sql server – Colleague says never us 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 analyzer 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).

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

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 telling me not to use OR. Can anyone lead me to these?

Here is the abomination before I updated it a long time ago. If you caught the @Department and are wondering about it, it is not an error. A Telerik reporting component is doing something to this code and expanding an array in the background before it gets to the server.

IF @Active = 2
BEGIN
--ACTIVE AND INACTIVE
    IF 0 IN (@Department)
    BEGIN
        IF @DOT = 1 
        BEGIN
            Select 
            A.LastName + ', ' + A.FirstName as EmployeeName,
            A.DepartmentName,
            C.Title,
            ISNULL(B.Comments, ' ') as Remarks,
            CONVERT(varchar, B.PassedDate, 101) as DateOut,
            D.Name as CompanyName

            FROM Employee A
            Left Outer Join EmployeeCourse B    ON A.Id = B.EmployeeId
            Left Outer Join CompanyCourse C     ON B.CompanyCourseId = C.Id
            Left Outer Join Company D           ON @companyId = D.Id

            WHERE   A.CompanyId = @companyId 
                    AND B.PassedDate IS Not NULL 
                    AND C.DotCourse = 1 

            ORDER BY EmployeeName, Title, PassedDate
        END

        IF @DOT = 0 
        BEGIN
            Select 
            A.LastName + ', ' + A.FirstName as EmployeeName,
            A.DepartmentName,           
            C.Title,
            ISNULL(B.Comments, ' ') as Remarks,
            CONVERT(varchar, B.PassedDate, 101) as DateOut,
            D.Name as CompanyName

            FROM Employee A
            Left Outer Join EmployeeCourse B    ON A.Id = B.EmployeeId
            Left Outer Join CompanyCourse C     ON B.CompanyCourseId = C.Id
            Left Outer Join Company D           ON @companyId = D.Id

            WHERE   A.CompanyId = @companyId 
                    AND B.PassedDate IS Not NULL 
                    
            ORDER BY EmployeeName, Title, PassedDate
        END

        IF @DOT = 2 
            BEGIN
            Select 
            A.LastName + ', ' + A.FirstName as EmployeeName,
            A.DepartmentName,           
            C.Title,
            ISNULL(B.Comments, ' ') as Remarks,
            CONVERT(varchar, B.PassedDate, 101) as DateOut,
            D.Name as CompanyName

            FROM Employee A
            Left Outer Join EmployeeCourse B    ON A.Id = B.EmployeeId
            Left Outer Join CompanyCourse C     ON B.CompanyCourseId = C.Id
            Left Outer Join Company D       ON @companyId = D.Id

            WHERE   A.CompanyId = @companyId 
                    AND B.PassedDate IS Not NULL 
                    AND C.DotCourse = 0 
                    
            ORDER BY EmployeeName, Title, PassedDate
        END 
    END
    ELSE
    BEGIN
        IF @DOT = 1 
        BEGIN
            Select 
            A.LastName + ', ' + A.FirstName as EmployeeName,
            A.DepartmentName,
            C.Title,
            ISNULL(B.Comments, ' ') as Remarks,
            CONVERT(varchar, B.PassedDate, 101) as DateOut,
            D.Name as CompanyName

            FROM Employee A
            Left Outer Join EmployeeCourse B    ON A.Id = B.EmployeeId
            Left Outer Join CompanyCourse C     ON B.CompanyCourseId = C.Id
            Left Outer Join Company D           ON @companyId = D.Id

            WHERE   A.CompanyId = @companyId 
                    AND B.PassedDate IS Not NULL 
                    AND C.DotCourse = 1
                    AND A.DepartmentId IN (@Department)

            ORDER BY EmployeeName, Title, PassedDate
        END

        IF @DOT = 0 
        BEGIN
            Select 
            A.LastName + ', ' + A.FirstName as EmployeeName,
            A.DepartmentName,
            C.Title,
            ISNULL(B.Comments, ' ') as Remarks,
            CONVERT(varchar, B.PassedDate, 101) as DateOut,
            D.Name as CompanyName

            FROM Employee A
            Left Outer Join EmployeeCourse B    ON A.Id = B.EmployeeId
            Left Outer Join CompanyCourse C     ON B.CompanyCourseId = C.Id
            Left Outer Join Company D           ON @companyId = D.Id

            WHERE   A.CompanyId = @companyId 
                    AND B.PassedDate IS Not NULL 
                    AND A.DepartmentId IN (@Department)
                    
            ORDER BY EmployeeName, Title, PassedDate
        END

        IF @DOT = 2 
        BEGIN
            Select 
            A.LastName + ', ' + A.FirstName as EmployeeName,
            A.DepartmentName,
            C.Title,
            ISNULL(B.Comments, ' ') as Remarks,
            CONVERT(varchar, B.PassedDate, 101) as DateOut,
            D.Name as CompanyName

            FROM Employee A
            Left Outer Join EmployeeCourse B    ON A.Id = B.EmployeeId
            Left Outer Join CompanyCourse C     ON B.CompanyCourseId = C.Id
            Left Outer Join Company D           ON @companyId = D.Id

            WHERE   A.CompanyId = @companyId 
                    AND B.PassedDate IS Not NULL 
                    AND C.DotCourse = 0 
                    AND A.DepartmentId IN (@Department)
                    
            ORDER BY EmployeeName, Title, PassedDate
        END 
    END
END
ELSE
BEGIN
--ACTIVE OR INACTIVE
    IF 0 IN (@Department)
    BEGIN
        IF @DOT = 1 
        BEGIN
            Select 
            A.LastName + ', ' + A.FirstName as EmployeeName,
            A.DepartmentName,
            C.Title,
            ISNULL(B.Comments, ' ') as Remarks,
            CONVERT(varchar, B.PassedDate, 101) as DateOut,
            D.Name as CompanyName

            FROM Employee A
            Left Outer Join EmployeeCourse B    ON A.Id = B.EmployeeId
            Left Outer Join CompanyCourse C     ON B.CompanyCourseId = C.Id
            Left Outer Join Company D           ON @companyId = D.Id

            WHERE  A.IsActive = @Active 
                    AND A.CompanyId = @companyId 
                    AND B.PassedDate IS Not NULL 
                    AND C.DotCourse = 1 

            ORDER BY EmployeeName, Title, PassedDate
        END

        IF @DOT = 0 
        BEGIN
            Select 
            A.LastName + ', ' + A.FirstName as EmployeeName,
            A.DepartmentName,
            C.Title,
            ISNULL(B.Comments, ' ') as Remarks,
            CONVERT(varchar, B.PassedDate, 101) as DateOut,
            D.Name as CompanyName

            FROM Employee A
            Left Outer Join EmployeeCourse B    ON A.Id = B.EmployeeId
            Left Outer Join CompanyCourse C     ON B.CompanyCourseId = C.Id
            Left Outer Join Company D           ON @companyId = D.Id

            WHERE  A.IsActive = @Active 
                    AND A.CompanyId = @companyId 
                    AND B.PassedDate IS Not NULL 
                    
            ORDER BY EmployeeName, Title, PassedDate
        END

        IF @DOT = 2 
            BEGIN
            Select 
            A.LastName + ', ' + A.FirstName as EmployeeName,
            A.DepartmentName,
            C.Title,
            ISNULL(B.Comments, ' ') as Remarks,
            CONVERT(varchar, B.PassedDate, 101) as DateOut,
            D.Name as CompanyName

            FROM Employee A
            Left Outer Join EmployeeCourse B    ON A.Id = B.EmployeeId
            Left Outer Join CompanyCourse C     ON B.CompanyCourseId = C.Id
            Left Outer Join Company D       ON @companyId = D.Id

            WHERE  A.IsActive = @Active 
                    AND A.CompanyId = @companyId 
                    AND B.PassedDate IS Not NULL 
                    AND C.DotCourse = 0 
                    
            ORDER BY EmployeeName, Title, PassedDate
        END 
    END
    ELSE
    BEGIN
        IF @DOT = 1 
        BEGIN
            Select 
            A.LastName + ', ' + A.FirstName as EmployeeName,
            A.DepartmentName,
            C.Title,
            ISNULL(B.Comments, ' ') as Remarks,
            CONVERT(varchar, B.PassedDate, 101) as DateOut,
            D.Name as CompanyName

            FROM Employee A
            Left Outer Join EmployeeCourse B    ON A.Id = B.EmployeeId
            Left Outer Join CompanyCourse C     ON B.CompanyCourseId = C.Id
            Left Outer Join Company D           ON @companyId = D.Id

            WHERE  A.IsActive = @Active
                    AND A.CompanyId = @companyId 
                    AND B.PassedDate IS Not NULL 
                    AND C.DotCourse = 1
                    AND A.DepartmentId IN (@Department)

            ORDER BY EmployeeName, Title, PassedDate
        END

        IF @DOT = 0 
        BEGIN
            Select 
            A.LastName + ', ' + A.FirstName as EmployeeName,
            A.DepartmentName,
            C.Title,
            ISNULL(B.Comments, ' ') as Remarks,
            CONVERT(varchar, B.PassedDate, 101) as DateOut,
            D.Name as CompanyName

            FROM Employee A
            Left Outer Join EmployeeCourse B    ON A.Id = B.EmployeeId
            Left Outer Join CompanyCourse C     ON B.CompanyCourseId = C.Id
            Left Outer Join Company D           ON @companyId = D.Id

            WHERE  A.IsActive = @Active 
                    AND A.CompanyId = @companyId 
                    AND B.PassedDate IS Not NULL 
                    AND A.DepartmentId IN (@Department)
                    
            ORDER BY EmployeeName, Title, PassedDate
        END

        IF @DOT = 2 
        BEGIN
            Select 
            A.LastName + ', ' + A.FirstName as EmployeeName,
            A.DepartmentName,
            C.Title,
            ISNULL(B.Comments, ' ') as Remarks,
            CONVERT(varchar, B.PassedDate, 101) as DateOut,
            D.Name as CompanyName

            FROM Employee A
            Left Outer Join EmployeeCourse B    ON A.Id = B.EmployeeId
            Left Outer Join CompanyCourse C     ON B.CompanyCourseId = C.Id
            Left Outer Join Company D           ON @companyId = D.Id

            WHERE  A.IsActive = @Active 
                    AND A.CompanyId = @companyId 
                    AND B.PassedDate IS Not NULL 
                    AND C.DotCourse = 0 
                    AND A.DepartmentId IN (@Department)
                    
            ORDER BY EmployeeName, Title, PassedDate
        END 
    END
END

NOTE I removed some in the initial code example to simplify.