SQL server has sometimes come under fire with the Oracle vs SQL Server debate because of the lack of some of the more advanced (and less used) functionality that is outlined in the SQL ANSI standards. An example of this is Window Functions, which became an ANSI standard under the ANSI:2003 revision. A Window function is an aggregate function that can be applied to a subset of a full set of data. Now this can be achieved in current versions of SQL Server (2008 R2 and its predecessors), but not using Window Functions and therefore has a performance implication. There are about 3 or 4 approaches to achieving a running total in SQL Server prior to the 2012 version, however none particularly elegant.
For my following example I will outline how to perform a running total using AdvertureWorks2008 sample data. I will remind us of 1 of the method’s (probably the most common) of how we used to do running totals prior to SQL Server 2012, and then show how to do the same running total using a SQL Server 2012 Window Function. To set the scene, we will be looking for a running total of Line Items for a given Order. This may be kind of query you may wish to write to generate an invoice with a running total on it.
To begin with lets look at the more traditional query:
SELECT
A.SalesOrderID,
A.SalesOrderDetailID,
A.LineTotal,
SUM(B.LineTotal)
FROM
Sales.SalesOrderDetail AS A
CROSS JOIN Sales.SalesOrderDetail AS B
WHERE
B.SalesOrderDetailID <= A.SalesOrderDetailID
AND
A.SalesOrderID = B.SalesOrderID
GROUP BY
A.SalesOrderID,
A.SalesOrderDetailID,
A.LineTotal
ORDER BY
A.SalesOrderID,
A.SalesOrderDetailID,
A.LineTotal
This simply works by self joining up to certain point, so works fine for any data that can be ordered easily, as running totals usually are this method usually suffices.
The following query uses the new Window function in SQL Server 2012:
SELECT
SalesOrderID,
SalesOrderDetailID,
LineTotal,
SUM(LineTotal)
OVER (PARTITION BY
SalesOrderID
ORDER BY
SalesOrderDetailID) AS OrderRunningTotal
FROM
Sales.SalesOrderDetail
ORDER BY
SalesOrderID,
SalesOrderDetailID,
LineTotal
You will instantly notice that the second query is far more elegant, and more simplistic to understand. In short, in this query, we are telling SQL server to Sum the “Line Total” over an ordered partition of the data. Both queries return the same result, however it becomes interesting when we look at the execution plans, relative to each other. This is shown below (apologies for the size of these):
What is important to note that the second (bottom) plan is smaller, and simpler. the other, very significant point is that the Query Cost (relative to batch) is a whopping 97% for the old method of running totals, meaning that the new Windowing Functions are far more efficient.
In summary, I believe that this is an example of where SQL server is becoming a firm competitor to some of its perceived rivals, its one of the few points that can be raised as a valid point in the argument of SQL Server vs Oracle, but not any more!
Passing DP-900 Azure Data Fundamentals
Back in December, I took the DP-900 Azure Data Fundamentals exam which is one of
Feb
Real-time Dashboards Provide Transparency to Everyone
Real-time dashboards enable data analytics firm Adatis to be agile and transparent with its team
Feb
Data is creating a new age of business agility
Developing a data strategy is central to ensuring your business can respond to disruptions and
Feb
Visualising your Infrastructure with Terraform Graph With VS-Code
Pre-Requisites Terraform VS-Code Terraform Extensions in VS-Code Overview An interesting and useful module that comes
Feb
Meet the Team – Kalina Ivanova, Junior Consultant
Next up in our series of Meet the Team blogs we are introducing you to
Feb
Meet the Team – Catherine Sachdev, Marketing Assistant
Next up we’re introducing you to Catherine Sachdev. Catherine joined us just over a year
Jan
Data Lineage with Azure Purview
I wrote an introductory Purview blog post previously, where I explored what the tool is
Jan
The Next Era of Retail: How Technology is driving change in a COVID-19 World
The retail sector is of great importance and accounts for almost 5% of GDP and
Jan