Click or drag to resize

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

The topic describes the SA0104 analysis rule.

Message

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

Description

The rule checks for queries combined with UNION ALL clause which use aggregate functions in the select list.

Consider rewriting the queries to use CASE function instead of combining the results of the queries using UNION.

Using CASE function within aggregates is an excellent way to add conditional logic to aggregates and to avoid hitting the same table multiple times with more simplistic aggregation.

Consider using CASE statements in conjunction with aggregation to write more robust and better performing queries.

Inefficient method which uses UNION ALL to combine the results:

SQL
 1SELECT COUNT(*) NumType1, NumType2 = 0, NumType3 = 0 FROM Test.Table1 
 2WHERE NumType = 'M'
 3UNION ALL
 4SELECT NumType1 = 0, COUNT(*) AS NumType2, NumType3 = 0 
 5FROM Test.Table1
 6WHERE NumType = 'C' 
 7UNION ALL
 8SELECT NumType1 = 0, NumType2 = 0, COUNT(*) 
 9FROM Test.Table1
10WHERE NumType = 'A'

More efficient method using the CASE function:

SQL
1SELECT 
2        NumType1 = SUM(CASE WHEN NumType = 'M' THEN 1 ELSE 0 END), 
3    NumType2 = SUM(CASE WHEN NumType = 'C' THEN 1 ELSE 0 END), 
4    NumType3 = SUM(CASE WHEN NumType = 'A' THEN 1 ELSE 0 END)
5FROM Test.Table1

Scope

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

Parameters
NameDescriptionDefault Value
RuleSuppressMark

Suppress rule reporting a rule violation in case a comment containing the value of this parameter is found close to the source of violation.

IGNORE:SA0104

Remarks

The rule requires SQL Connection. If there is no connection provided, the rule will be skipped during analysis.

Categories

Design Rules

Additional Information

There is no additional info for this rule.

Example Test Script
SQL
 1SELECT COUNT(*) NumType1, NumType2 = 0, NumType3 = 0 FROM Test.Table1 
 2WHERE NumType = 'M'
 3UNION ALL
 4SELECT NumType1 = 0, COUNT(*) AS NumType2, NumType3 = 0 
 5FROM Test.Table1
 6WHERE NumType = 'C' 
 7UNION ALL
 8SELECT NumType1 = 0, NumType2 = 0, COUNT(*) 
 9FROM Test.Table1
10WHERE NumType = 'A'

Analysis Results

 MessageLineColumn
1SA0104 : Use CASE statements in conjunction with aggregation to write more robust and better performing queries.70
See Also

Other Resources