SA0104 : Use CASE statements in conjunction with aggregation to write more robust and better performing queries

Introduction

Using the UNION ALL clause with aggregate functions may lead to unexpected results.

Description

When working with T-SQL and SQL Server, using UNION ALL to combine query results is common. However, if the select lists contain aggregate functions without proper grouping or filtering, it could lead to misleading or incorrect data outputs. This often happens when the intention is to simply append results, but the use of aggregates without distinct groups can skew data interpretation.

For example:

-- Example of a problematic query
SELECT SUM(Sales) FROM SalesData1
UNION ALL
SELECT SUM(Sales) FROM SalesData2;

The above query combines total sales from two different data sets. However, if the data sets contain overlapping or unfiltered data, the aggregated results may be misleading, showing cumulative totals that do not reflect distinct groupings or data subsets.

The absence of a GROUP BY clause can lead to aggregated data results that do not accurately represent distinct categories or groups.
The UNION ALL operation does not eliminate duplicates, risking inflated aggregate results if there’s overlapping data across combined tables.

How to fix

This section provides guidance on rewriting queries to use the CASE function instead of combining results with UNION ALL for improved performance and more accurate data aggregation.

Follow these steps to address the issue:

1.Analyze the existing query to understand how UNION ALL is used with aggregate functions. Identify the conditions and data sources being unified.

2.Replace the UNION ALL with a single query using the CASE function. This approach allows you to conditionally sum data within the same query, avoiding multiple scans over the same table.

3.Implement the CASE function inside aggregate functions to calculate conditional sums. Incorporate each condition that previously required separate SELECT statements.

For example:

-- Inefficient method using UNION ALL
SELECT COUNT(*) AS NumType1, 0 AS NumType2, 0 AS NumType3 FROM Test.Table1 WHERE NumType = 'M'
UNION ALL
SELECT 0 AS NumType1, COUNT(*) AS NumType2, 0 AS NumType3 FROM Test.Table1 WHERE NumType = 'C'
UNION ALL
SELECT 0 AS NumType1, 0 AS NumType2, COUNT(*) AS NumType3 FROM Test.Table1 WHERE NumType = 'A';

-- Efficient method using CASE
SELECT
    SUM(CASE WHEN NumType = 'M' THEN 1 ELSE 0 END) AS NumType1,
    SUM(CASE WHEN NumType = 'C' THEN 1 ELSE 0 END) AS NumType2,
    SUM(CASE WHEN NumType = 'A' THEN 1 ELSE 0 END) AS NumType3
FROM Test.Table1;

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Rule has no parameters.

Remarks

The rule requires Analysis Context. If context is missing, the rule will be skipped during analysis.

Effort To Fix

1 hour per issue.

Additional Information

There is no additional info for this rule.

Example Test SQL

SELECT COUNT(*) NumType1, NumType2 = 0, NumType3 = 0 FROM Test.Table1
WHERE NumType = 'M'
UNION ALL
SELECT NumType1 = 0, COUNT(*) AS NumType2, NumType3 = 0
FROM Test.Table1
WHERE NumType = 'C'
UNION ALL
SELECT NumType1 = 0, NumType2 = 0, COUNT(*)
FROM Test.Table1
WHERE NumType = 'A'

Analysis Results

	Message	Line	Column
1	SA0104 : Use CASE statements in conjunction with aggregation to write more robust and better performing queries.	7	0

Analysis Rules