Home

Description

This rule states that when using the COALESCE, IIF, and CASE functions in SQL Server, the input expressions should not contain subqueries. Subqueries should not be used because they can cause performance issues, as well as make the code more difficult to read and maintain. Instead, use a simple expression or variable.

Key Benefits

• Slow Down the Query: It should not contain subqueries because subqueries can slow down the query execution time.
• Increase the Complexity: It should not contain subqueries because subqueries can increase the complexity of the query.
• Complex and Difficult to Maintain: It should not contain subqueries because subqueries can cause the query to become too complex and difficult to maintain.

Non-compliant Code Example

SELECT 
COALESCE((SELECT Top 1 Color FROM Customer Where Id=10), 'Blue'),      --Non compliant code (Clause containing sub queries)
IIF((SELECT COUNT(*) FROM Customer) < 100, 'Blue', 'Red'),       --Non compliant code (Clause containing sub queries)
CASE  
    WHEN (SELECT COUNT(*) FROM Customer) < 100 THEN 'Blue'       --Non compliant code (Clause containing sub queries)
    WHEN (SELECT COUNT(*) FROM Customer) < 200 THEN 'Red'       --Non compliant code (Clause containing sub queries)
    ELSE
        'Black'
    END As CaseResult
FROM Production.Product;

Compliant Code Example

Declare @customer INT;
SELECT  @customer = COUNT(*) FROM Customer;

Declare @Color1 varchar(50);
SELECT Top 1 @Color1= Color FROM Sales.Customer Where Id=10;

SELECT 
COALESCE(@Color1, 'Blue'),     --Compliant code 
IIF(@customer < 100, 'Blue', 'Red'),     --Compliant code 
CASE  
    WHEN @customer < 100 THEN 'Blue'     --Compliant code 
    WHEN @customer < 200 THEN 'Red'    --Compliant code 
    ELSE
        'Black'
    END As CaseResult
FROM Production.Product;