Archive
SQL Server 2012 (a.k.a Denali) – New feature | Enhanced OVER() Clause – Part1
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
SQL Server 2012 (a.k.a Denali) – New feature | WITH RESULT SETS
“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