SA0106 : Avoid OR operator in queries

Avoid OR operator in queries

The rule checks SELECT, UPDATE and DELETE statements for use of the OR operator in their filtering clauses.

Often, the OR operator can confuse SQL Server and prevent it from coming up with a good query plan.

Check the Query Plan and look for undesirable behavior such as index scans or table spools.
If a seek is performed, check to make sure that is it seeking on all of the intended columns, rather than performing a WHERE filter on columns that should otherwise be seekable.

The most common case of expensive OR-s comes from queries with optional search parameters, of the form:

1WHERE (Col1 = @Param1 AND @Param1 IS NOT NULL) OR 
2      (Col2 = @Param2 AND @Param2 != ) OR 
3      (Col3 = @Param3 AND @Param3 != 0)

The best solution for this sort of query is to dynamically construct a SQL string based on whichever input parameters apply, and pass the SQL string to sp_executesql with a set of parameters.

It is preferable to avoid putting parameters as literals into a string which will cause a recompile for each parameter value.

Example:

 1-- Using dynamic SQL
 2CREATE TABLE #Tbl
 3(
 4    ID INT NOT NULL,
 5    Col1 VARCHAR(50) NOT NULL,
 6    Col2 VARCHAR(50) NOT NULL,
 7    PRIMARY KEY CLUSTERED (ID)
 8)
 9INSERT INTO #Tbl VALUES (1, 'abcd', '')
10INSERT INTO #Tbl VALUES (2, '123', 'abc')
11
12DECLARE @Sql NVARCHAR(1000), @Param1 VARCHAR(50), @Param2 VARCHAR(50)
13SELECT @Param1 = '', @Param2 = 'abc'
14
15SET @Sql = N'SELECT ID FROM #Tbl WHERE 1=1' + 
16    CASE WHEN @Param1 != '' THEN ' AND Col1 = @Param1' ELSE '' END +
17    CASE WHEN @Param2 != '' THEN ' AND Col2 = @Param2' ELSE '' END
18
19EXEC dbo.sp_executesql @Sql, N'@Param1 VARCHAR(50), @Param2 VARCHAR(50)', @Param1, @Param2
20
21--DROP TABLE #Tbl

Less common forms of expensive OR-s can sometimes be dealt with UNIONS, multiple LEFT JOINs, and various other SQL techniques available.

Example:

1SELECT ID FROM #Tbl WHERE Col1 = @Param1
2UNION ALL
3SELECT ID FROM #Tbl WHERE Col2 = @Param2

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

The rule does not need Analysis Context or SQL Connection.

Design Rules, Bugs

There is no additional info for this rule.

 1CREATE PROCEDURE [dbo].testsp_SA00106
 2(
 3 @param1  nvarchar(20),
 4 @param2  nvarchar(20),
 5 @param3  nvarchar(20)
 6)
 7AS
 8
 9SELECT BusinessEntityID, Name 
10FROM Sales.Store
11WHERE (BusinessEntityID LIKE @param1 OR SalesPersonID LIKE @param3) AND
12      (Name LIKE @param2 OR @param2 IS NULL);

Analysis Results

Other Resources