SA0033 : Do not use the GROUP BY clause without an aggregate function

Using GROUP BY without aggregate functions can lead to inefficiencies in query execution.

Description

When you construct a query using GROUP BY without any aggregate functions, you may inadvertently affect performance. Though your intention might be to remove duplicates, using GROUP BY lacks the performance benefits that DISTINCT can provide, particularly in older SQL Server versions.

For example:

SQL
1-- Example of a problematic query
2SELECT ColumnName FROM TableName GROUP BY ColumnName;

This query attempts to eliminate duplicate values in ColumnName but uses GROUP BY without aggregates, potentially causing unnecessary computation. In newer SQL Server versions, execution plans for GROUP BY without aggregates and DISTINCT are equivalent, making this practice outdated.

  • Unnecessary complexity in query design when simple deduplication is needed.

  • Potentially slower execution in older versions of SQL Server, where GROUP BY can introduce additional overhead.

How to fix

Optimize queries using GROUP BY without aggregate functions for performance improvements.

Follow these steps to address the issue:

  1. Review the query that uses GROUP BY without aggregate functions to determine the intention behind removing duplicates.

  2. Replace GROUP BY with DISTINCT to achieve deduplication efficiently.

  3. Execute the modified query and analyze its performance using SQL Server Management Studio’s Execution Plan feature to ensure efficiency improvements.

For example:

SQL
1-- Example of corrected query using DISTINCT
2SELECT DISTINCT ColumnName FROM TableName;

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
2 minutes per issue.
Categories

Performance Rules, Code Smells

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
 1-- GROUP BY clause used without aggregate function in order to return distinct rows
 2SELECT     ColumnA ,
 3           ColumnB
 4FROM       T
 5GROUP BY   ColumnA ,
 6           ColumnB
 7
 8-- The statement returns equivalent results as the above         
 9SELECT DISTINCT
10           ColumnA ,
11           ColumnB
12FROM       T

Analysis Results
  Message Line Column
1 SA0033 : Do not use the GROUP BY clause without an aggregate function. 5 0
See Also

Other Resources