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

Introduction

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:

-- Example of a problematic query
SELECT 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:

-- Example of corrected query using DISTINCT
SELECT 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.

Additional Information

There is no additional info for this rule.

Example Test SQL

-- GROUP BY clause used without aggregate function in order to return distinct rows
SELECT     ColumnA ,
           ColumnB
FROM       T
GROUP BY   ColumnA ,
           ColumnB

-- The statement returns equivalent results as the above
SELECT DISTINCT
           ColumnA ,
           ColumnB
FROM       T

Analysis Results

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

Analysis Rules