Row-by-Row Processing - SQL Server Query Performance Tuning - page 468

SELECT SUM(page_count)
FROM sys.dm_db_index_physical_stats(DB_ID(N'AdventureWorks2012'),
OBJECT_ID('Production.WorkOrder'),
DEFAULT, DEFAULT, DEFAULT);

■ Note the sys.dm_db_index_physical_stats DMV is explained in detail in Chapter 13.

In most cases, you can avoid cursor operations by rewriting the functionality using SQL queries, concentrating
on set-based methods of accessing the data. For example, you can rewrite the preceding stored procedure using SQL
queries (instead of the cursor operations) as follows ( nocursor.sql in the download):

IF (SELECT OBJECT_ID('dbo.TotalLoss')
) IS NOT NULL
DROP PROC dbo.TotalLoss;
GO
CREATE PROC dbo.TotalLoss
AS
SELECT CASE --Determine status based on following computation
WHEN SUM(MoneyLostPerProduct) > 5000 THEN 'We are bankrupt!'
ELSE 'We are safe!'
END AS Status
FROM (--Calculate total money lost for all discarded products
SELECT SUM(wo.ScrappedQty * p.ListPrice) AS MoneyLostPerProduct
FROM Production.WorkOrder AS wo
JOIN Production.ScrapReason AS sr
ON wo.ScrapReasonID = sr.ScrapReasonID
JOIN Production.Product AS p
ON wo.ProductID = p.ProductID
GROUP BY p.ProductID
) DiscardedProducts;
GO

In this stored procedure, the aggregation functions of SQL Server are used to compute the money lost per product
and the total loss. The CASE statement is used to determine the business status based on the total loss incurred. The
stored procedure can be executed as follows; but again, you should execute it twice, so you can see the results of
plan caching:

EXEC dbo.TotalLoss;

Figure 22-4 shows the corresponding Extended Events output.