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.
