SA0106 : Avoid OR operator in queries

Introduction

The OR operator in SQL queries can lead to suboptimal query performance.

Description

Using the OR operator in SELECT , UPDATE , and DELETE statements may cause issues in generating efficient query execution plans. SQL Server might struggle to optimize the use of OR , potentially leading to slower query performance and increased resource usage.

For example:

-- Example of problematic query using OR
SELECT * FROM Employees WHERE DepartmentId = 1 OR LocationId = 2;

This query can be problematic because SQL Server may create a less efficient query plan that scans more rows than necessary. This inefficiency can occur due to the optimizer’s difficulty in properly estimating the cost of combining multiple predicates with OR . Strategies such as query rewriting, using UNION , or additional indexing might be needed for more efficient execution.

Reduces query performance and speed due to inefficient execution plans.
Increases resource consumption, impacting the overall system performance.

How to fix

Optimize the use of the OR operator in SQL queries to enhance performance and reduce resource consumption.

Follow these steps to address the issue:

1.Check the query plan in SQL Server Management Studio (SSMS) for performance bottlenecks such as index scans or table spools. Ensure that columns intended for seeking are properly indexed and utilized in the queries.

2.Identify queries with optional search parameters using the OR operator, such as WHERE (Col1 = @Param1 AND @Param1 IS NOT NULL) OR (Col2 = @Param2 AND @Param2 != ) . Consider dynamically constructing SQL strings based on actual input parameters and execute with sp_executesql to optimize these queries.

3.Rewrite queries using UNIONs instead of OR to potentially improve performance, as UNIONs can make execution plans more efficient. Evaluate combinations or alternatives like multiple LEFT JOINs where applicable.

4.Avoid hard-coding parameter values into SQL strings. Use parameterized queries to take advantage of the query plan cache and avoid recompiling.

For example, using dynamic SQL to tailor queries:

CREATE TABLE #Tbl
(
    ID INT NOT NULL,
    Col1 VARCHAR(50) NOT NULL,
    Col2 VARCHAR(50) NOT NULL,
    PRIMARY KEY CLUSTERED (ID)
);
INSERT INTO #Tbl VALUES (1, 'abcd', '');
INSERT INTO #Tbl VALUES (2, '123', 'abc');
DECLARE @Sql NVARCHAR(1000), @Param1 VARCHAR(50), @Param2 VARCHAR(50);
SELECT @Param1 = '', @Param2 = 'abc';
SET @Sql = N'SELECT ID FROM #Tbl WHERE 1=1' +
    CASE WHEN @Param1 != '' THEN ' AND Col1 = @Param1' ELSE '' END +
    CASE WHEN @Param2 != '' THEN ' AND Col2 = @Param2' ELSE '' END;
EXEC dbo.sp_executesql @Sql, N'@Param1 VARCHAR(50), @Param2 VARCHAR(50)', @Param1, @Param2;
--DROP TABLE #Tbl;

In some cases, employ UNIONs for improved efficiency:

SELECT ID FROM #Tbl WHERE Col1 = @Param1
UNION ALL
SELECT ID FROM #Tbl WHERE Col2 = @Param2;

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Name	Description	Default Value
IgnoreOrWithIsNull	The parameter can ignore OR operators which have one of their operand be IS NULL comparison expression.	yes

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix

1 hour per issue.

Additional Information

There is no additional info for this rule.

Example Test SQL

CREATE PROCEDURE [dbo].testsp_SA00106
(
 @param1  nvarchar(20),
 @param2  nvarchar(20),
 @param3  nvarchar(20)
)
AS

SELECT BusinessEntityID, Name
FROM Sales.Store
WHERE (BusinessEntityID LIKE @param1 OR SalesPersonID LIKE @param3) AND
      (Name LIKE @param2 OR @param2 IS NULL);

Analysis Results

	Message	Line	Column
1	SA0106 : Avoid OR operator in queries.	11	37

Analysis Rules