CUBE, ROLLUP, COMPUTE, COMPUTE BY, GROUPING SETS

The CUBE and ROLLUP operators are useful in generating reports that contain subtotals and totals. There are extensions of the GROUP BY clause.

Difference b/w CUBE and ROLLUP:
- CUBE generates a result set that shows aggregates for all combinations of values in the selected columns.
- ROLLUP generates a result set that shows aggregates for a hierarchy of values in the selected columns.

select 'A' [class], 1 [rollno], 'a' [section], 80 [marks], 'manoj' stuName

into #tempTable

UNION

select 'A', 2, 'a', 70 ,'harish'

UNION

select 'A', 3, 'a', 80 ,'kanchan'

UNION

select 'A', 4, 'b', 90 ,'pooja'

UNION

select 'A', 5, 'b', 90 ,'saurabh'

UNION

select 'A', 6, 'b', 50 ,'anita'

UNION

select 'B', 1, 'a', 60 ,'nitin'

UNION

select 'B', 2, 'a', 50 ,'kamar'

UNION

select 'B', 3, 'a', 80 ,'dinesh'

UNION

select 'B', 4, 'b', 90 ,'paras'

UNION

select 'B', 5, 'b', 50 ,'lalit'

UNION

select 'B', 6, 'b', 70 ,'hema'

select class, rollno, section, marks, stuName from #tempTable

output

class	rollno	section	marks	stuName
A	1	a	80	manoj
A	2	a	70	harish
A	3	a	80	kanchan
A	4	b	90	pooja
A	5	b	90	saurabh
A	6	b	50	anita
B	1	a	60	nitin
B	2	a	50	kamar
B	3	a	80	dinesh
B	4	b	90	paras
B	5	b	50	lalit
B	6	b	70	hema

WITH ROLLUP:

select class, section, sum(marks) [sum]

from #tempTable

group by class, section with ROLLUP

Output

class	section	sum
A	a	230
A	b	230
A	NULL	460
B	a	190
B	b	210
B	NULL	400
NULL	NULL	860

WITH CUBE:

select class, section, sum(marks) [sum]

from #tempTable

group by class, section with CUBE

Output

Output:

class   section sum

A       a       230

A       b       230

A       NULL    460  -- 230 + 230  = 460

B       a       190

B       b       210

B       NULL    400  -- 190 + 210 = 400

NULL    NULL    860  -- 460 + 400 = 860

NULL    a       420  -- 230 + 190 = 420

NULL    b       440  -- 230 + 210 = 440

COMPUTE & COMPUTE BY:
A COMPUTE BY clause allows you to see both detail and summary rows with one SELECT statement. You can calculate summary values for subgroups, or a summary value for the whole result set.
The COMPUTE clause takes the following information:
- The optional BY keyword. This calculates the specified row aggregate on a per column basis.
- A row aggregate function name. This includes SUM, AVG, MIN, MAX, or COUNT.
- A column upon which to perform the row aggregate function.

select class, section, marks

from #tempTable

COMPUTE SUM(marks), AVG(marks)

select class, section, marks

from #tempTable

order by class

COMPUTE SUM(marks), AVG(marks) by class

select class, section, marks

from #tempTable

order by class, section

COMPUTE SUM(marks), AVG(marks) by class, section

GROUPING SETS:
SQL Server 2008 has a new GROUPING SETS operator which can generate the same result set as that generated by using a simple GROUP BY, ROLLUP, or CUBE operator.

Precision, Scale, and Length (Transact-SQL)

Precision is the number of digits in a number.

Scale is the number of digits to the right of the decimal point in a number.

For example, the number 123.45 has a precision of 5 and a scale of 2.

Length for a numeric data type is the number of bytes that are used to store the number

Length for a character string or Unicode data type is the number of characters.

The length for binary, varbinary, and image data types is the number of bytes. 

For example, an int data type can hold 10 digits, is stored in 4 bytes, and does not accept decimal points. The int data type has a precision of 10, a length of 4, and a scale of 0.

Default maximum precision of numeric and decimal data types is 38.

Get Identity of a Table

SELECT IDENT_CURRENT(TableName')