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.

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

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:
-
Remove the GROUP BY ALL clause from your query.
-
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.
-
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:
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;

The rule has a Batch scope and is applied only on the SQL script.

Rule has no parameters.

The rule does not need Analysis Context or SQL Connection.


Design Rules, Deprecated Features, Bugs

There is no additional info for this rule.

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 |

Message | Line | Column | |
---|---|---|---|
1 | SA0198 : Usage of deprecated GROUP BY ALL syntax encountered. | 7 | 0 |
