Comparison of supported GROUP BY features

queries

#tsql#queries

For a

clause that uses

, or

, the maximum number of

expressions is 32. The maximum number of groups is 4,096 (2

). The following examples fail

because the

clause has more than 4,096 groups.

The following example generates 4,097 (2

The following example generates 4,097 (2

and

the

grouping set produce a grand total row and duplicate grouping sets aren’t

eliminated.

This example uses the backward compatible syntax. It generates 8,192 (2

) grouping sets

and then fails.

For backward compatible

clauses that don’t contain

, the

column sizes, the aggregated columns, and the aggregate values involved in the query

limit the number of

items. This limit originates from the limit of 8,060 bytes on

the intermediate worktable that holds intermediate query results. You can use a maximum

of 12 grouping expressions when you specify

or.

The following table describes the

features that different products support.

Integration Services

aggregates

Not supported for

or.

Supported for

, or

Expand table

GROUP BY

ROLLUP

CUBE

GROUPING SETS

GROUP BY

CUBE ()

()

GROUP BY

CUBE

ROLLUP

GROUP
BY

GROUP BY

CUBE

ROLLUP

GROUP BY

DISTINCT

WITH CUBE

WITH
ROLLUP

WITH CUBE

WITH
ROLLUP

GROUPING SETS

CUBE

GROUP BY GROUPING SETS( CUBE(a1,., a12), b)

GROUP BY GROUPING SETS( CUBE(a1,., a12), ())

GROUP BY CUBE (a1,., a13)
GROUP BY a1,., a13
WITH
CUBE