SA0106 : Avoid OR operator in queries
The topic describes the SA0106 analysis rule.
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.
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.
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.
Suppress rule reporting a rule violation in case a comment containing the value of this parameter is found close to the source of violation.
The parameter can ignore OR operators which have one of their operand be IS NULL comparison expression.
The rule does not need Analysis Context or SQL Connection.
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);
|1||SA0106 : Avoid OR operator in queries.||11||37|
© Ubitsoft Ltd. All Rights Reserved.