Upgrading from an older version of SQL Server to a newer one can sometimes lead to unexpected issues. In this blog post, we will discuss one such issue that arose during an upgrade from SQL Server 2005 to SQL Server 2012.

After the upgrade, it was discovered that certain reports were not working and were throwing errors. Upon closer inspection, it was found that the reports were using the deprecated COMPUTE BY clause, which is no longer supported in SQL Server 2012. The COMPUTE BY clause has been replaced by the ROLL UP clause in SQL Server 2012.

However, replacing COMPUTE BY with ROLL UP is not a straightforward task. The primary reason for this is the difference in how each clause returns results. COMPUTE BY returns multiple result sets, while ROLL UP returns all the results in a single result set.

Let’s take a look at an example to understand the difference:

CREATE TABLE tblPopulation (
    Country VARCHAR(100),
    [State] VARCHAR(100),
    City VARCHAR(100),
    [Population (in Millions)] INT
)

INSERT INTO tblPopulation VALUES ('India', 'Delhi', 'East Delhi', 9)
INSERT INTO tblPopulation VALUES ('India', 'Delhi', 'South Delhi', 8)
INSERT INTO tblPopulation VALUES ('India', 'Delhi', 'North Delhi', 5.5)
INSERT INTO tblPopulation VALUES ('India', 'Delhi', 'West Delhi', 7.5)
INSERT INTO tblPopulation VALUES ('India', 'Karnataka', 'Bangalore', 9.5)
INSERT INTO tblPopulation VALUES ('India', 'Karnataka', 'Belur', 2.5)
INSERT INTO tblPopulation VALUES ('India', 'Karnataka', 'Manipal', 1.5)
INSERT INTO tblPopulation VALUES ('India', 'Maharastra', 'Mumbai', 30)
INSERT INTO tblPopulation VALUES ('India', 'Maharastra', 'Pune', 20)
INSERT INTO tblPopulation VALUES ('India', 'Maharastra', 'Nagpur', 11)
INSERT INTO tblPopulation VALUES ('India', 'Maharastra', 'Nashik', 6.5)

SELECT Country, [State], City, SUM([Population (in Millions)]) AS [Population (in Millions)]
FROM tblPopulation
GROUP BY Country, [State], City WITH ROLLUP

SELECT Country, [State], City, [Population (in Millions)]
FROM tblPopulation
ORDER BY Country, [State], City
COMPUTE SUM([Population (in Millions)]) BY Country, [State]

In the above example, we have a table called “tblPopulation” which stores population data for different cities in India. We want to calculate the total population for each city, state, and country.

The first query uses the ROLL UP clause to group the data and calculate the total population at different levels (city, state, and country). The result is returned in a single result set.

The second query uses the COMPUTE BY clause to calculate the total population for each country and state. The result is returned in separate result sets for each country and state.

After analyzing this example, it becomes clear that using ROLL UP is a much easier and efficient way to replace COMPUTE BY. It eliminates the need to rewrite the code and provides a more concise and unified result set.

So, if you are facing a similar situation where you need to replace COMPUTE BY in your SQL Server code, consider using ROLL UP as a better alternative.

Thank you for reading this blog post. If you have any questions or suggestions, please feel free to leave a comment below.