SA0167 : Non-ISO standard comparison operator found

Using non-ISO standard comparison operators in SQL queries can lead to issues with cross-platform compatibility and future-proofing your code.

Description

When writing T-SQL code in SQL Server, it’s important to adhere to ISO standard comparison operators to ensure your queries run smoothly across different RDBMS platforms and future SQL Server versions.

For example:

SQL
Copy
1-- Non-ISO standard operator example
2SELECT * FROM TableName WHERE Column != 'Value';

This example uses !=, a non-standard operator. In contrast, using <> is the ISO standard for not equal comparisons.

  • Non-ISO operators might not be recognized in other SQL databases, leading to compatibility issues.

  • Using non-standard operators may result in unexpected behavior or errors in future versions of SQL Server.

How to fix

To ensure cross-platform compatibility and future-proofing of your T-SQL code, replace non-ISO standard comparison operators with ISO standard operators in SQL queries.

Follow these steps to address the issue:

  1. Identify any non-ISO standard operators in your SQL query. Common non-ISO operators include != for not equal, !< for greater than or equal to, and !> for less than or equal to.

  2. Replace != with <>, which is the ISO standard operator for not equal comparisons.

  3. Replace !< with >=, which is the ISO standard operator for greater than or equal comparisons.

  4. Replace !> with <=, which is the ISO standard operator for less than or equal comparisons.

For example:

SQL
Copy
1-- Example of corrected query using ISO standard operators
2SELECT * FROM TableName WHERE Column <> 'Value';

Scope

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

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
2 minutes per issue.
Categories

Design Rules, Code Smells

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 1-- Test Case 1: The violation should be reported
 2SELECT Column1 FROM Table1 WHERE Column1 != 1
 3-- Test Case 2: The violation should be reported
 4SELECT Column1 FROM Table1 WHERE Column1 !< 1
 5-- Test Case 3: The violation should be reported
 6SELECT Column1 FROM Table1 WHERE Column1 !> 1
 7
 8-- Test Case 4: A violation should not be reported
 9SELECT Column1 FROM Table1 WHERE Column1 <> 1
10-- Test Case 5: A violation should not be reported
11SELECT Column1 FROM Table1 WHERE Column1 >= 1
12-- Test Case 6: A violation should no be reported
13SELECT Column1 FROM Table1 WHERE Column1 <= 1
Example Test SQL with Automatic Fix
SQL
Copy
 1-- Test Case 1: The violation should be reported
 2SELECT Column1 FROM Table1 WHERE Column1 <> 1
 3-- Test Case 2: The violation should be reported
 4SELECT Column1 FROM Table1 WHERE Column1 >= 1
 5-- Test Case 3: The violation should be reported
 6SELECT Column1 FROM Table1 WHERE Column1 <= 1
 7
 8-- Test Case 4: A violation should not be reported
 9SELECT Column1 FROM Table1 WHERE Column1 <> 1
10-- Test Case 5: A violation should not be reported
11SELECT Column1 FROM Table1 WHERE Column1 >= 1
12-- Test Case 6: A violation should no be reported
13SELECT Column1 FROM Table1 WHERE Column1 <= 1
Analysis Results
  Message Line Column
1 SA0167 : Non-ISO standard comparison operator found: != 2 41
2 SA0167 : Non-ISO standard comparison operator found: !< 4 41
3 SA0167 : Non-ISO standard comparison operator found: !> 6 41
See Also

Other Resources

Analysis Rules