April | 2012 | SQL with Manoj

SQL Server 2012 (a.k.a Denali) – New feature | Enhanced OVER() Clause – Part1

April 13, 2012 1 comment

OVER() clause in SQL Server was introduced in version 2005 and it was the same in 2008 and even 2008-R2.

Ranking functions like ROW_NUMBER(), RANK(), DENSE_RANK() & NTILE() can use the OVER() clause to Parition and Order a record-set before actually applying their logic. Thus a large record-set can be divided into several partitions and calculations can be applied to each set in a set-based approach rather than going with Loops or Cursors.

With the new version of SQL Server i.e. 2012 the OVER() clause has been extended completely to other aggregates, like SUM(), AVG(), etc. Prior to this the OVER() clause can be partially used for these Aggregate Functions.

Here we will see solving a classic problem of calculating Running Totals with this new feature.
I’ve already discussed about solving this problem with JOINS in version 2005,2008, 2008-R2 in my previous post at following [link].

Let’s see how can we use the SUM() Aggregate Function with OVER() clause to solve this:

USE [AdventureWorks2012]
GO

-- With 2012, calculating Running totals by using Aggregare function SUM() with extended Window Function OVER():
;with RunTot as (
	select 
		row_number() over(order by s.OrderDate) as row,
		s.SalesPersonID, p.FirstName, p.LastName, s.TotalDue, s.OrderDate
	from Sales.SalesOrderHeader s
	join Person.Person p
	on s.SalesPersonID = p.BusinessEntityID
	)
SELECT 
	SalesPersonID, FirstName, LastName, OrderDate, TotalDue, 
	SUM(TotalDue) OVER(partition by SalesPersonID ORDER BY row)
FROM RunTot
order by SalesPersonID, row
GO


-- Prior to 2012 calculating Running totals by using SELF-JOIN:
;with RunTot as (
	select 
		row_number() over(partition by s.SalesPersonID order by s.OrderDate) as row,
		s.SalesPersonID, p.FirstName, p.LastName, s.TotalDue, s.OrderDate
	from Sales.SalesOrderHeader s
	join Person.Person p
	on s.SalesPersonID = p.BusinessEntityID)
select a.row, a.SalesPersonID, a.FirstName, a.LastName, a.OrderDate, a.TotalDue, sum(b.TotalDue) as RunTotal
from RunTot a
join RunTot b on a.SalesPersonID = b.SalesPersonID and a.row >= b.row
group by a.row, a.SalesPersonID, a.FirstName, a.LastName, a.TotalDue, a.OrderDate
order by a.SalesPersonID, a.row

On comparing performance of both the queries within the batch:
– The first query with SUM() window function costs just 38%.
– While the second query without the SUM() window function by using JOINS costs 68%.

I still like to call SQL Server’s latest release by its code name “Denali”, not “SQL Server 2012”.

Stay tuned to other new features of Denali in my forthcomming posts.

For more information about the OVER() clause check MS BOL link here: http://msdn.microsoft.com/en-us/library/ms189461.aspx

Categories: SQL Server 2012, SQL Tips Tags: Aggregate Functions, Denali, OVER clause, RUNNING TOTALS, SQL Server 2012

SQL Server 2012 (a.k.a Denali) – New feature | WITH RESULT SETS

April 12, 2012 4 comments

“WITH RESULT SETS”, a new feature added in SQL Server 2012 allows us to tweak the Column Names and their Datatypes returned by an SP upon executed as per our needs. In SQL Server 2012 while calling an SP you can add the “WITH RESULT SETS” option and can provide the new Column Names and/or with new Datatypes with the parenthesis as per your wish (shown below).

Prior to this if one has to do the same, he has to either:
– Edit the same SP with new column names, or
– Create a new duplicate SP with different column headers, or
– Push records to a temp table first then use the records set form that table (discussed in later part of this post).

Let’s check this new feature by a simple example here by using a Stored Procedure from [AdventureWorks2012] database for SQL Server 2012:

USE [AdventureWorks2012]
GO

-- Normal SP call without using "WITH RESULT SETS" option:
EXEC [dbo].[uspGetEmployeeManagers] @BusinessEntityID = 100

Recordset with same headers as in SP:

RecursionLevel	BusinessEntityID	FirstName	LastName	OrganizationNode	ManagerFirstName	ManagerLastName
0		100			Lolan		Song		/3/1/9/7/		Kok-Ho			Loh
1		93			Kok-Ho		Loh		/3/1/9/			Peter			Krebs
2		26			Peter		Krebs		/3/1/			James			Hamilton
3		25			James		Hamilton	/3/			Ken			Sánchez

-- This is how you can use the new option and get desired headers:
EXEC [dbo].[uspGetEmployeeManagers] @BusinessEntityID = 100
WITH RESULT SETS 
(
	(
	Level INT,
	BusinessID INT, 
	EmpFirstName VARCHAR(50), 
	EmpLastName VARCHAR(50), 
	OrgNode VARCHAR(20), 
	ManagerFirstName VARCHAR(50), 
	ManagerLastName VARCHAR(50)
	)
)

Recordset with new headers as in SP:

Level	BusinessID	EmpFirstName	EmpLastName	OrgNode		ManagerFirstName	ManagerLastName
0	100		Lolan		Song		/3/1/9/7/	Kok-Ho			Loh
1	93		Kok-Ho		Loh		/3/1/9/		Peter			Krebs
2	26		Peter		Krebs		/3/1/		James			Hamilton
3	25		James		Hamilton	/3/		Ken			Sánchez

–> Traditonal appraoch prior to ver. 2012

-- Create a table with columns with required names:
CREATE TABLE #tempData (
	Level INT,
	BusinessID INT, 
	EmployeeFirstName VARCHAR(50), 
	EmployeeLastName VARCHAR(50), 
	OrgNode VARCHAR(20), 
	ManagerFirstName VARCHAR(50), 
	ManagerLastName VARCHAR(50)
	)

-- Insert records from the SP to the table:
INSERT INTO #tempData (Level, BusinessID, EmployeeFirstName, EmployeeLastName, OrgNode, ManagerFirstName, ManagerLastName)
EXEC @return_value = [dbo].[uspGetEmployeeManagers] @BusinessEntityID = 100

-- Finaly select the table and use the records:
SELECT * FROM #tempData

-- Final Cleanup:
DROP TABLE #tempData
GO

Well this is OK, but not very exciting feature for me as a developer.
I was hoping this would allow me to add/remove the columns and/or Concatenate them, like FirstName & LastName to FullName, but No!!!

For more information you can check MS BOL, here: http://msdn.microsoft.com/en-us/library/ms188332(v=sql.110).aspx