SA0167 : Non-ISO standard comparison operator found

Introduction

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:

-- Non-ISO standard operator example
SELECT * 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:

-- Example of corrected query using ISO standard operators
SELECT * 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.

Additional Information

There is no additional info for this rule.

Example Test SQL

-- Test Case 1: The violation should be reported
SELECT Column1 FROM Table1 WHERE Column1 != 1
-- Test Case 2: The violation should be reported
SELECT Column1 FROM Table1 WHERE Column1 !< 1
-- Test Case 3: The violation should be reported
SELECT Column1 FROM Table1 WHERE Column1 !> 1

-- Test Case 4: A violation should not be reported
SELECT Column1 FROM Table1 WHERE Column1 <> 1
-- Test Case 5: A violation should not be reported
SELECT Column1 FROM Table1 WHERE Column1 >= 1
-- Test Case 6: A violation should no be reported
SELECT Column1 FROM Table1 WHERE Column1 <= 1

Example Test SQL with Automatic Fix

-- Test Case 1: The violation should be reported
SELECT Column1 FROM Table1 WHERE Column1 <> 1
-- Test Case 2: The violation should be reported
SELECT Column1 FROM Table1 WHERE Column1 >= 1
-- Test Case 3: The violation should be reported
SELECT Column1 FROM Table1 WHERE Column1 <= 1

-- Test Case 4: A violation should not be reported
SELECT Column1 FROM Table1 WHERE Column1 <> 1
-- Test Case 5: A violation should not be reported
SELECT Column1 FROM Table1 WHERE Column1 >= 1
-- Test Case 6: A violation should no be reported
SELECT 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

Analysis Rules