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

Introduction

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:

-- Example of potentially problematic usage with sub-queries
SELECT 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:

-- Original, potentially problematic usage
SELECT COALESCE((SELECT TOP 1 columnName FROM TableName ORDER BY SomeColumn), 'DefaultValue') AS Result;

-- Refactored query for consistent results
;WITH SubQueryCTE AS (
    SELECT TOP 1 columnName FROM TableName ORDER BY SomeColumn
)
SELECT COALESCE(SubQueryCTE.columnName, 'DefaultValue') AS Result
FROM 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.

Additional Information

CASE (Transact-SQL)

COALESCE (Transact-SQL)

IIF (Transact-SQL)

Example Test SQL

SELECT  COALESCE( (SELECT  col1
                   FROM  table1
                   WHERE  col2 > 0), 1)

SELECT  CASE(SELECT  COUNT(*)
             FROM  sys.objects)
            WHEN 1 THEN 1
            ELSE 2
        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

Analysis Rules