SA0038 : The comparison expression evaluates to TRUE

Comparison expressions that are always true can uncover logical errors in a SQL query.

Description

This problem arises when a SQL query contains comparison logic that is flawed, resulting in conditions that will inevitably evaluate to TRUE. Such logical errors can lead to inefficient queries, returning unintended results, or excessive data processing SQL Server environments.

For example:

SQL
Copy
1-- Example of a problematic query
2SELECT * FROM Employees WHERE 1 = 1;

In the above example, the condition 1 = 1 is always true, meaning that the filter does not restrict the result set at all. This can lead to inefficient queries where unnecessary data is retrieved, potentially impacting performance and complicating debugging efforts.

  • Queries may return more data than intended, affecting performance and increasing workload on SQL Server.

  • Logical flaws can cause confusion and make query logic harder to maintain and understand.

How to fix

Correct flawed comparison expressions to improve the logic and efficiency of SQL queries.

Follow these steps to address the issue:

  1. Identify comparison expressions within your SQL queries that may always evaluate to TRUE. These comparisons can often be found in WHERE clauses.

  2. Review the logic of each identified expression. Determine if it is necessary or if it results from a logical error. For example, a condition like 1 = 1 is always TRUE and should be removed.

  3. Replace or remove unnecessary comparisons. Adjust your query to appropriately filter data. Make sure the conditions accurately reflect the intended logical criteria for the result set.

  4. Test the revised query to ensure it returns the intended results and operates efficiently.

For example:

SQL
Copy
1-- Example of corrected query
2SELECT * FROM Employees WHERE EmployeeStatus = 'Active';

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
3 minutes per issue.
Categories

Design Rules, Code Smells

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 1-- Comparison will always evaluate to TRUE. 
 2-- *****************************************
 3
 4SELECT * FROM sys.objects o
 5WHERE 
 61 = 1 AND
 70 != 1 AND
 82 > 1 AND 
 92>=2 AND 
102>=1 AND
112>=2.0 AND 
122>=1.0 AND 
13object_id = [object_id]  AND 
14o.object_id = o.[object_id]  AND
15'ab' = N'ab' AND
16'ab' > N'a' AND
17'ab' >= N'a' AND 
18'ab' != N'a' AND 
19'ab' = 'ab' AND
20'ab' > 'a' AND
21'ab' >= 'a' AND 
22'ab' != 'a' AND 
23name = name AND 
24[name] = [name] AND 
25o.[name] = [o].[name] AND 
26--name >= name AND
27name IN (name,'aaa','bbb') AND 
28'a' IN ('a','b','c','d') AND
291 IN (2,4,3,1) AND
301 NOT IN (2,4,3) AND
311 NOT IN (2,4,3-2) AND
32name LIKE name AND
33Exists (SELECT 0)
34-- AND dbo.myFunction(a) = dbo.myFunction(a)   -- Only if deterministic function
35
36SELECT * FROM sys.objects
37WHERE 
38object_id = [object_id]  OR 
39sys.objects.object_id = sys.objects.[object_id]
40
41SELECT * FROM sys.objects WHERE Exists (SELECT 0 WHERE 1  = 0) 
42SELECT * FROM sys.objects WHERE NOT Exists (SELECT 0 WHERE 1 = 1)
Analysis Results
  Message Line Column
1 SA0038 : The comparison expression evaluates to TRUE. 27 5
2 SA0038 : The comparison expression evaluates to TRUE. 28 4
3 SA0038 : The comparison expression evaluates to TRUE. 29 2
4 SA0038 : The comparison expression evaluates to TRUE. 30 6
5 SA0038 : The comparison expression evaluates to TRUE. 32 5
6 SA0038 : The comparison expression evaluates to TRUE. 33 0
7 SA0038 : The comparison expression evaluates to TRUE. 6 2
8 SA0038 : The comparison expression evaluates to TRUE. 7 2
9 SA0038 : The comparison expression evaluates to TRUE. 8 2
10 SA0038 : The comparison expression evaluates to TRUE. 9 1
…
28 SA0038 : The comparison expression evaluates to TRUE. 39 22
29 SA0038 : The comparison expression evaluates to TRUE. 42 61
See Also

Other Resources

Analysis Rules