DF082: COALESCE, IIF, or CASE is used incorrectly.

Last modified: December 25, 2024

The topic describes the DF082 T-SQL code analysis rule.

Category

PERFORMANCE

Message

COALESCE, IIF, or CASE is used incorrectly.

Description

It is not recommended to use COALESCE, IIF, and CASE input expressions that include sub-queries as they will be evaluated multiple times.

Additional information

Using COALESCE, IIF, and CASE with sub-query input expressions is not recommended because these sub-queries are evaluated multiple times, leading to increased execution time, higher resource utilization, redundant data retrieval, and the potential for inconsistent results. To mitigate these issues, consider using Common Table Expressions (CTEs), derived tables, or temporary tables, which allow the sub-query to be evaluated once and reused, improving performance and consistency.

Noncompliant code example

SELECT COALESCE((SELECT AccountNumber FROM Sales.Customer WHERE CustomerId = 10), '1', '2')

SELECT CASE (SELECT AccountNumber FROM Sales.Customer WHERE CustomerId = 10)
    WHEN 'a' THEN 1
    WHEN 'b' THEN 2
    WHEN 'c' THEN 3
    ELSE 99
  END

Compliant solution

DECLARE @AccountNumber VARCHAR(128)
SELECT @AccountNumber = AccountNumber FROM sales.Customer WHERE CustomerId = 10
 
SELECT COALESCE(@AccountNumber,'1','2')
 
SELECT CASE @AccountNumber
    WHEN 'a' THEN 1
    WHEN 'b' THEN 2
    WHEN 'c' THEN 3
    ELSE 99
END