SA0102 : Do not use DISTINCT keyword in aggregate functions

Introduction

Performance issues can arise from using DISTINCT in aggregate functions in SQL Server.

Description

Including the DISTINCT keyword in aggregate functions like SUM , COUNT , or AVG can lead to significant performance degradation in T-SQL queries, especially when combined with multiple aggregates or other operations. This is because the DISTINCT keyword demands additional processing to eliminate duplicates before performing the aggregation, thus increasing the workload on the database server.

For example:

-- Example of problematic query
SELECT COUNT(DISTINCT Supplier), COUNT(*) FROM TrOrderPO WHERE OrderNum = '10101234';

In this query, using DISTINCT within the COUNT function forces SQL Server to scan the data set to remove duplicate Supplier values before counting, which can slow down performance when dealing with large datasets.

Increased query execution time due to the overhead of distinguishing unique values.
Potential strain on server resources, impacting overall database performance.

How to fix

Avoid using DISTINCT in aggregate functions to improve query performance.

Follow these steps to address the issue:

1.Identify queries where the DISTINCT keyword is used within aggregate functions like SUM , COUNT , or AVG .

2.Remove the DISTINCT keyword from the aggregate function and evaluate if the query can be rewritten to achieve the same results with a more efficient approach.

3.Consider using separate queries or temporary tables to pre-process the data and remove duplicates before performing the aggregation.

4.Optimize indexes on the columns used in WHERE clauses or joins to enhance the performance of the query execution.

For example:

-- Example of corrected query without DISTINCT in aggregate function
SELECT COUNT(Supplier) FROM (SELECT DISTINCT Supplier FROM TrOrderPO WHERE OrderNum = '10101234') AS DistinctSuppliers;

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.

Additional Information

There is no additional info for this rule.

Example Test SQL

SELECT
COUNT_BIG(DISTINCT Supplier),
COUNT(*)
FROM TrOrderPO
WHERE OrderNum = '10101234'
HAVING COUNT(DISTINCT Supplier) > 100

SELECT AVG(DISTINCT ListPrice),SUM(DISTINCT ListPrice)
FROM Production.Product;

SELECT CHECKSUM_AGG(CAST(Quantity AS int)), CHECKSUM_AGG(DISTINCT CAST(Quantity AS int))
FROM Production.ProductInventory;

SELECT STDEVP(Bonus), STDEVP(DISTINCT Bonus)
FROM Sales.SalesPerson;

SELECT VAR(Bonus), VAR(DISTINCT Bonus),VARP(Bonus),VARP(DISTINCT Bonus)
FROM 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

Analysis Rules