SA0199 : Usage of deprecated COMPUTE clause encountered

Introduction

The COMPUTE clause is deprecated and should not be used in T-SQL queries for SQL Server 2012 and later.

Description

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:

-- Example of using deprecated COMPUTE clause
SELECT column1, column2
FROM TableName
ORDER BY column1
COMPUTE 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.

How to fix

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:

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

Additional Information

There is no additional info for this rule.

Example Test SQL

SELECT Country, State, City, Population
FROM Country C
ORDER BY Name, State, City
COMPUTE SUM(Population) BY Country, State

SELECT Country, State, City, SUM (Population) AS Population
FROM Country C
GROUP BY Country, State, City
WITH ROLLUP

Analysis Results

	Message	Line	Column
1	SA0199 : Usage of deprecated COMPUTE clause encountered.	4	0

Analysis Rules