SQL Server Portal

A couple of days ago, I developed a tool how to recover the dropped index without backup. In this tool, running total needs to be generated. So I did some research and came across couple of options along with the best option as well.

Given below are the three methods :

Method 1:
In this method, we will use the self join to calculate running total.

--This script will work on SQL Server 2005 and above.
USE AdventureWorks2012
GO
SELECT
A.SalesOrderID AS [SalesOrderID]
, A.SalesOrderDetailID AS [SalesOrderDetailID]
, A.ProductID AS [ProductID]
, A.LineTotal AS [LineTotal]
, SUM(B.lineTotal) AS [Running Total]

FROM SALES.SalesOrderDetail A
INNER JOIN Sales.SalesOrderDetail B
ON A.SalesOrderID =B.SalesOrderID
AND B.SalesOrderDetailID<=A.SalesOrderDetailID
WHERE A.SalesOrderID =43659
GROUP BY
A.SalesOrderID
, A.SalesOrderDetailID
, A.ProductID
, A.LineTotal
ORDER BY
A.SalesOrderID
, A.SalesOrderDetailID
, A.ProductID
, A.LineTotal
GO

Method 2:
In this method, we will use the sub query to calculate running total.

--This script will work on SQL Server 2005 and above
USE AdventureWorks2012
GO
SELECT
A.SalesOrderID AS [SalesOrderID]
, A.SalesOrderDetailID AS [SalesOrderDetailID]
, A.ProductID AS [ProductID]
, A.LineTotal AS [LineTotal]
, (
SELECT SUM(B.lineTotal)
FROM SALES.SalesOrderDetail B
WHERE A.SalesOrderID =B.SalesOrderID
AND B.SalesOrderDetailID<=A.SalesOrderDetailID
) AS [Running Total]
FROM SALES.SalesOrderDetail A
WHERE A.SalesOrderID =43659
GO

Method 3:
In this method, we will use the SUM function to calculate running total.

--This script will work on SQL Server 2008 R2 and above.
USE AdventureWorks2012
GO
SELECT
SalesOrderID
, SalesOrderDetailID
, ProductID
, LineTotal
, SUM(LineTotal)
OVER(PARTITION BY SalesOrderID
ORDER BY SalesOrderID,SalesOrderDetailID)
AS [Running Total]
FROM SALES.SalesOrderDetail
WHERE SalesOrderID =43659
GO

All of the above queries will give you the same result set. However lets view their performance .

Conclusion :
As you can see, the performance of Method 3 is better than other methods. Also, if you look at the line of code, it is also shorter in Method 3 vis-à-vis other methods. So, I would recommend method 3.

Read Full Post »