Difference between CUBE and WITH CUBE

Question

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'

I don't think there is a difference. To be honest, I usually use `grouping sets` so the summary dimensions are explicit. — Gordon Linoff, Aug 06 '18 at 14:47

GSerg · Answer 1 · 2018-08-06T15:05:14.847

The difference is stated in the documentation:

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

GROUP BY {
      column-expression  
    | ROLLUP ( <group_by_expression> [ ,...n ] )  
    | CUBE ( <group_by_expression> [ ,...n ] )  
    | GROUPING SETS ( <grouping_set> [ ,...n ]  )  
    | () --calculates the grand total 
} [ ,...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 { CUBE | ROLLUP } ]   

and:

Feature   SQL Server             SQL Server                SQL Server 2008 or later
          Integration Services   compatibility level 100   with compatibility level 90
                                 or higher

CUBE      Not supported          Supported                 Not supported

WITH CUBE Supported              Supported                 Supported

score 0 · Answer 2 · answered Aug 06 '18 at 14:50

0

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

answered Aug 06 '18 at 14:50

Milney

6,253
2
19
33

Difference between CUBE and WITH CUBE

2 Answers2