Database Reference
In-Depth Information
Analyzing Overhead with T-SQL Cursors
The T-SQL cursors implemented using T-SQL statements are always executed on SQL Server because they need
the SQL Server engine to process their T-SQL statements. You can use a combination of the cursor characteristics
explained previously to reduce the overhead of these cursors. As mentioned earlier, the most lightweight T-SQL
cursor is the one created, not with the default settings but by manipulating the settings to arrive at the forward-only
read-only cursor. That still leaves the T-SQL statements used to implement the cursor operations to be processed by
SQL Server. The complete load of the cursor is supported by SQL Server without any help from the client machine.
Suppose an application requirement results in the following list of tasks that must be supported:
Production.WorkOrder
table) that have been scrapped.
•
Identify all products (from the
•
For each scrapped product, determine the money lost, where the money lost per product
equals the units in stock times the unit price of the product.
•
Calculate the total loss.
•
Based on the total loss, determine the business status.
The
FOR EACH
phrase in the second point suggests that these application tasks could be served by a cursor.
However, a
FOR
,
WHILE
, cursor, or any other kind of processing of this type can be dangerous within SQL Server. Let's
see how it works with a cursor. You can implement this application requirement using a T-SQL cursor as follows:
IF (SELECT OBJECT_ID('dbo.TotalLoss_CursorBased')
) IS NOT NULL
DROP PROC dbo.TotalLoss_CursorBased;
GO
CREATE PROC dbo.TotalLoss_CursorBased
AS --Declare a T-SQL cursor with default settings, i.e., fast
--forward-only to retrieve products that have been discarded
DECLARE ScrappedProducts CURSOR
FOR
SELECT p.ProductID,
wo.ScrappedQty,
p.ListPrice
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;
--Open the cursor to process one product at a time
OPEN ScrappedProducts;
DECLARE @MoneyLostPerProduct MONEY = 0,
@TotalLoss MONEY = 0;
