SA0198 : Usage of deprecated GROUP BY ALL syntax encountered

The GROUP BY ALL syntax in SQL Server is deprecated and poses several challenges in query optimization and functionality.

Description

The GROUP BY ALL clause was designed to include all groups in the result set, irrespective of whether they satisfy the search criteria defined in the WHERE clause. However, this syntax is outdated and will eventually be phased out, which can lead to maintenance issues and performance limitations.

For example:

SQL
1-- Example of deprecated syntax
2SELECT Column1, SUM(Column2) FROM TableName GROUP BY ALL Column1;

In this example, GROUP BY ALL Column1 is used, which could result in NULL values for groups that do not match the WHERE conditions. Additionally, this approach might not work with remote tables and conflicts with columns that use the FILESTREAM attribute.

  • Using GROUP BY ALL may hinder query performance as SQL Server has deprecated it, making future maintenance more cumbersome.

  • Compatibility issues arise because GROUP BY ALL is not functional with remote tables when combined with a WHERE clause and fails with FILESTREAM columns.

How to fix

Avoid using deprecated GROUP BY ALL syntax due to potential maintenance and performance issues. Instead, refactor queries to achieve the desired result without it.

Follow these steps to address the issue:

  1. Remove the GROUP BY ALL clause from your query.

  2. Reassess the query logic to achieve the intended result set. For instance, utilize left joins or different query restructuring approaches to include all desired groups.

  3. If necessary, introduce a LEFT JOIN to ensure all groups are included, even if they don’t meet the original filtering criteria of a WHERE clause.

For example:

SQL
1-- Refactored query without GROUP BY ALL
2SELECT Column1, SUM(Column2)
3FROM TableName
4LEFT JOIN AnotherTable ON TableName.Column1 = AnotherTable.Column1
5GROUP BY TableName.Column1;

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

Design Rules, Deprecated Features, Bugs

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
 1-- Deprecated GROUP BY ALL clause used
 2SELECT     
 3        ColumnA ,
 4        COUNT(DISTINCT ColumnB) 
 5FROM    T
 6WHERE   ColumnC > 1000
 7GROUP BY ALL  
 8        ColumnA
 9
10-- The statement is equivalent as the above         
11SELECT  ColumnA ,
12        COUNT(DISTINCT CASE WHEN ColumnC > 1000 THEN ColumnB END) 
13FROM    T
14GROUP BY 
15        ColumnA

Analysis Results
  Message Line Column
1 SA0198 : Usage of deprecated GROUP BY ALL syntax encountered. 7 0
See Also

Other Resources