SA0023 : Avoid using not equal operator (<>,!=) in the WHERE clause

The topic describes the SA0023 analysis rule.

Message

Avoid using not equal operator (<>,!=) in the WHERE clause

Description

The rule checks for usage of the not equal operator in the WHERE clause as it result table and index scans.

How to fix

Consider replacing the not equal operator with equals (=) or inequality operators (>,>=,<,<=) if possible.

Scope

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

Parameters
Name Description Default Value
IgnoreColumnsFromTempTables

Ignore not equal comparison of columns of a temporary table or table variable.

yes
Remarks

The rule requires SQL Connection. If there is no connection provided, the rule will be skipped during analysis.

Effort To Fix
20 minutes per issue.
Categories

Performance Rules, Code Smells

Additional Information
Example Test SQL
SQL
Copy
 1CREATE INDEX IX_Table1_value ON Table1 (value)
 2
 3ALTER TABLE Table1 ADD computed_value AS (CASE WHEN value = 'value1' then 1 ELSE 0 END)
 4
 5CREATE INDEX IX_Table1_computed_value ON Table1(computed_value)
 6
 7-- Not equal operatror used in the WHERE clause.
 8SELECT * 
 9FROM  Table1 t1
10WHERE t1.value <> 'value1';
11
12-- Equal operatror used in the WHERE clause.
13SELECT * 
14FROM Table1 t1
15WHERE t1.computed_value = 0
Analysis Results
  Message Line Column
1 SA0023 : Avoid using not equal operator (<>,!=) in the WHERE clause. 10 15
See Also

Other Resources

Analysis Rules