Clash Royale CLAN TAG#URR8PPP

Difference between CUBE and WITH CUBE

I found in MSSQL exists both

SELECT <list of columns>, sum(measure) as measure
FROM fact-table
GROUP BY <list of columns> WITH CUBE


and

SELECT <list of columns>, sum(measure) as measure
FROM fact-table
GROUP BY CUBE (<list of columns>)


I was wandering what is the difference, because using them, I get same results with both syntaxes.

Also I remember that, at least for version 2008, WITH CUBE was supported by express edition and ROLLUP(), CUBE() and GROUPING SETS() were a feature of 'Enterprise editions'

grouping sets

2 Answers
2

The difference is stated in the documentation:

Syntax for SQL Server and Azure SQL Database

ISO-Compliant Syntax

GROUP BY GROUPING SETS ( <grouping_set> [ ,...n ] )
[ ,...n ]


For backward compatibility only.

Non-ISO-Compliant Syntax for SQL Server and Azure SQL Database

GROUP BY
[ ALL ] column-expression [ ,...n ]
| column-expression [ ,...n ] [ WITH ROLLUP ]


and:

I believe the WITH keyword is optional in all contexts except starting a CTE declaration, in this case - both do the exact same thing

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.