Click or drag to resize

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
1SELECT COUNT(DISTINCT Supplier), COUNT(*) FROM TrOrderPO WHERE OrderNum = '10101234'

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:SA0102

Remarks

The rule does not need Analysis Context or SQL Connection.

Categories

Design Rules

Additional Information

There is no additional info for this rule.

Example Test Script
SQL
 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

 MessageLineColumn
1SA0102 : Do not use DISTINCT keyword in aggregate functions.210
2SA0102 : Do not use DISTINCT keyword in aggregate functions.613
3SA0102 : Do not use DISTINCT keyword in aggregate functions.811
4SA0102 : Do not use DISTINCT keyword in aggregate functions.835
5SA0102 : Do not use DISTINCT keyword in aggregate functions.1157
6SA0102 : Do not use DISTINCT keyword in aggregate functions.1429
7SA0102 : Do not use DISTINCT keyword in aggregate functions.1723
8SA0102 : Do not use DISTINCT keyword in aggregate functions.1756
See Also

Other Resources