SQL with Manoj

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