SA0173 : COALESCE, IIF, and CASE input expressions containing sub-queries will be evaluated multiple times

Sub-queries used as input expressions in COALESCE, IIF, and CASE can lead to unexpected results and performance issues.

Description

Using sub-queries within the COALESCE, IIF, or CASE functions can create problems because each sub-query might be evaluated multiple times. This can result in inconsistent data returned by the expression, especially if the database’s isolation level allows for differing results in concurrent transactions.

For example:

SQL
Copy
1-- Example of potentially problematic usage with sub-queries
2SELECT COALESCE((SELECT TOP 1 columnName FROM TableName ORDER BY SomeColumn), 'DefaultValue') AS Result;

In this example, the sub-query inside COALESCE might be executed more than once, possibly yielding different results each time, leading to unpredictable behavior. This is exacerbated by isolation levels that may expose uncommitted changes or by modifications between executions.

  • Unpredictable query results due to multiple evaluations of the sub-query.

  • Potential performance degradation because the sub-query is computed multiple times.

How to fix

Move sub-queries out of expressions like COALESCE, IIF, and CASE to ensure consistent results and better performance.

Follow these steps to address the issue:

  1. Identify the sub-query used within the COALESCE, IIF, or CASE expressions.

  2. Refactor the query by executing the sub-query separately and storing its result in a variable or a common table expression (CTE).

  3. Use the result of the sub-query from the variable or CTE in your COALESCE, IIF, or CASE expressions to avoid multiple evaluations.

For example:

SQL
Copy
1-- Original, potentially problematic usage
2SELECT COALESCE((SELECT TOP 1 columnName FROM TableName ORDER BY SomeColumn), 'DefaultValue') AS Result;
3
4-- Refactored query for consistent results
5;WITH SubQueryCTE AS (
6    SELECT TOP 1 columnName FROM TableName ORDER BY SomeColumn
7)
8SELECT COALESCE(SubQueryCTE.columnName, 'DefaultValue') AS Result
9FROM SubQueryCTE;

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

Design Rules, Bugs

Additional Information
Example Test SQL
SQL
Copy
1SELECT  COALESCE( (SELECT  col1
2                   FROM  table1
3                   WHERE  col2 > 0), 1)
4
5SELECT  CASE(SELECT  COUNT(*)
6             FROM  sys.objects)
7            WHEN 1 THEN 1
8            ELSE 2
9        END
Analysis Results
  Message Line Column
1 SA0173 : A subquery contained in the input expression of the COALESCE function. The subquery will be evaluated more than once. 1 8
2 SA0173 : A subquery contained in the input expression of the CASE function. The subquery will be evaluated more than once. 5 8
See Also

Other Resources

Analysis Rules