SA0199 : Usage of deprecated COMPUTE clause encountered

The problem addressed here is the use of the deprecated COMPUTE clause in SQL Server queries. Although it might have been useful in previous versions, it is no longer supported from SQL Server 2012 onwards, rendering any queries using it potentially incompatible with newer systems.

For example:

1-- Example of using deprecated COMPUTE clause
2SELECT column1, column2
3FROM TableName
4ORDER BY column1
5COMPUTE SUM(column2);

This query will not work in SQL Server versions 2012 and later because the COMPUTE clause is not supported. It may cause errors or unexpected behavior.

• Query compatibility issues with modern versions of SQL Server.

• Potential errors or performance impacts due to unsupported syntax.

The COMPUTE clause is deprecated in SQL Server 2012 and later. Replace it with the ROLLUP feature to ensure compatibility and leverage optimized query performance.

Follow these steps to address the issue:

1. Identify SQL queries that use the COMPUTE clause. These queries need modification to ensure compatibility with SQL Server 2012 and later.

2. Replace the COMPUTE clause with a ROLLUP operation. This involves modifying the query to include ROLLUP in the GROUP BY clause.

3. Update application code and logic to handle the single result set returned by ROLLUP instead of the multiple result sets returned by COMPUTE BY.

4. Test the modified queries thoroughly to ensure they produce the desired results and improve performance where possible.

For example:

1-- Example of corrected query using ROLLUP
2SELECT column1, 
3       SUM(column2) AS Total
4FROM TableName
5GROUP BY column1 WITH ROLLUP
6ORDER BY 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.

Other Resources