T-SQL Enhancements in SQL Server 2008 phase-3

Hello,

we have seen some features of sql server 2008 in my previous article like merge statement, compund assignment operators, declare and initialize variables, Row constructors etc. Now today i am going to describe grouping sets.

Describe Grouping Sets :

  • Grouping set: a set of group by columns
  • Helps supporting dynamic analysis of aggregates
  • SQL Server versions earlier than 2008 had limited support for grouping sets
  • SQL Server 2008 introduces several important enhancements supporting grouping sets

Grouping Sets Enhancements in 2008 :

  • New ISO-compliant subclauses in GROUP BY clause:
    • GROUPING SETS
    • CUBE
    • ROLLUP
  • New GROUPING_ID function identifies grouping set

GROUPING SETS Subclause :

  • Define multiple grouping sets in a single query
  • NULLs used as placeholders

CUBE and ROLLUP Subclauses :

  • CUBE: abbreviation for a power set of grouping sets constructed from input elements (2^n grouping sets for n elements)
  • ROLLUP: abbreviation for the subset of grouping sets that is relevant in a hierarchy scenario (n+1 grouping sets for n elements)

GROUPING_ID :

  • Accepts a list of elements as input
  • Produces an integer bitmap that identifies the grouping set
  • Each bit represents a different element
  • Bit off (0) – element participated in current grouping set
  • Bit on (1) – element did not participate in current grouping set

Example :

CREATE TABLE [dbo].[employee]
(
	[Employee_Number] [int] NOT NULL PRIMARY KEY,
	[Employee_Name] [varchar](30) NULL,
	[Salary] [float] NULL,
	[Department_Number] [int] NULL,
[Region] [varchar](30) NULL
) ON [PRIMARY]

INSERT INTO employee
VALUES
(1,'Sachin',5000,1,'Ahmedabad'),
(2,'Rahul',9000,1,'Baroda'),
(3,'Saurav',4000,2,'Ahmedabad'),
(4,'Yuvraj',8000,1,'Delhi'),
(5,'Zahir',8000,2,'Delhi')

SELECT Region, Department_number, AVG(salary) Average_Salary
FROM Employee
GROUP BY
GROUPING SETS
(
	(Region, Department_number),
	(Region),
	(Department_number) ,
	()                            
)

--UNION results of above query
SELECT Region, Department_number, avg(salary) Average_Salary
from Employee
Group BY
Region, Department_number
UNION
SELECT Region, NULL, avg(salary) Average_Salary
from Employee
Group BY
Region
UNION
SELECT NULL, Department_number, avg(salary) Average_Salary
from Employee
Group BY
Department_number
UNION
SELECT NULL, NULL, avg(salary) Average_Salary
from Employee

--CUBE  REPLACEMENT:
SELECT Region, Department_Number, avg(salary) Average_Salary
from Employee
Group BY
CUBE (Region, Department_Number)  

--ROLLUP  REPLACEMENT:
SELECT Region, Department_Number, avg(salary) Average_Salary
from Employee
Group BY
ROLLUP (Region, Department_Number) 
--(Region, Department_Number),(Region)()

SELECT Region, Department_Number, avg(salary) Average_Salary
from Employee
Group BY
ROLLUP (Department_Number, Region) 
--(Department_Number, Region),(Department_Number)()

SELECT GROUPING_ID
(
	Region,						--2
	Department_Number			--1
)AS grp_ID, Region, Department_Number FROM Employee
Group BY
CUBE (Region, Department_Number)

SELECT GROUPING_ID
(
	Region,						--4
	Department_Number,			--2
	salary						--1
)AS grp_ID, Region, salary Department_Number FROM Employee
Group BY
GROUPING SETS(salary, 
CUBE (Region, Department_Number))

Hope you enjoyed this; Now I will explain phase-4 in coming days.

Leave a Reply

Your email address will not be published. Required fields are marked *


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>