SA0102 : Do not use DISTINCT keyword in aggregate functions

The topic describes the SA0102 analysis rule.

Message

Do not use DISTINCT keyword in aggregate functions

Description

The rule checks all aggregate functions (except MIN and MAX) for using the DISTINCT keyword.

The using DISTINCT in aggregate function can often cause significant performance degradation especially when used multiple times or with other aggregate functions in the same select.

Example of using DISTINCT in aggregate function:

SQL
Copy
1SELECT COUNT(DISTINCT Supplier), COUNT(*) FROM TrOrderPO WHERE OrderNum = '10101234'

How to fix

Review the query and avoid use of DISTINCT in aggregate functions.

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
1 hour per issue.
Categories

Design Rules, Bugs

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 1SELECT 
 2COUNT_BIG(DISTINCT Supplier), 
 3COUNT(*) 
 4FROM TrOrderPO 
 5WHERE OrderNum = '10101234'
 6HAVING COUNT(DISTINCT Supplier) > 100
 7
 8SELECT AVG(DISTINCT ListPrice),SUM(DISTINCT ListPrice)
 9FROM Production.Product;
10
11SELECT CHECKSUM_AGG(CAST(Quantity AS int)), CHECKSUM_AGG(DISTINCT CAST(Quantity AS int))
12FROM Production.ProductInventory;
13
14SELECT STDEVP(Bonus), STDEVP(DISTINCT Bonus)
15FROM Sales.SalesPerson;
16
17SELECT VAR(Bonus), VAR(DISTINCT Bonus),VARP(Bonus),VARP(DISTINCT Bonus)
18FROM Sales.SalesPerson;
Analysis Results
  Message Line Column
1 SA0102 : Do not use DISTINCT keyword in aggregate functions. 2 10
2 SA0102 : Do not use DISTINCT keyword in aggregate functions. 6 13
3 SA0102 : Do not use DISTINCT keyword in aggregate functions. 8 11
4 SA0102 : Do not use DISTINCT keyword in aggregate functions. 8 35
5 SA0102 : Do not use DISTINCT keyword in aggregate functions. 11 57
6 SA0102 : Do not use DISTINCT keyword in aggregate functions. 14 29
7 SA0102 : Do not use DISTINCT keyword in aggregate functions. 17 23
8 SA0102 : Do not use DISTINCT keyword in aggregate functions. 17 56
See Also

Other Resources

Analysis Rules