Handling Cursor-Friendly Problems in T-SQL: Running Totals Example

By design, the SQL programming language works with data sets, so it is not surprising that for most problems, row by row processing is not nearly as efficient as data set processing. There is a class of problems, however, that is much easier to solve using cursors. A typical "cursor friendly" problem is one where the data set returned contains at least one column whose value depends on column values from one or more previous rows of the same row set. Even when a data set based solution exists, it is hard to build a query that is more efficient than a cursor based solution. Usually, advanced coding techniques have to be used, and sometimes a special index design is required for the tables involved. For that type of problem, it is usually better to send a simple row set to the calling application and let the client cycle through the rows and calculate the column value derived.

The Problem

Let's define the problem as displaying cumulative sales for a specific store and product. This is a simplified example since I haven't included the sales period, which would be more realistic. I don't want to include a discussion about handling time periods as a part of the criteria in Transact-SQL. That topic deserves a separate article.

Let's say there is a sales table with the structure below:

CREATE TABLE [dbo].[Sales] (
     [TransactionID] [int] IDENTITY (1, 1) NOT NULL,
     [StoreID] [int] NOT NULL,
     [productID] [int] NOT NULL,
     [transactionTime] [datetime] NOT NULL,
     [amount] [money] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Sales] WITH NOCHECK ADD
     CONSTRAINT [PK_Sales] PRIMARY KEY CLUSTERED
     (
          [TransactionID]
     ) ON [PRIMARY]
GO

To populate the table you can use this script:

set noCount on
declare @i int
set @i = 0
while @i < 100000 begin

     insert into dbo.Sales(StoreID, productId, transactionTime, amount)
     select 1,
          p.productID,
          dateAdd(minute, - @i, getDate()),
          case (p.ProductID)
           when 1 then 10
           when 2 then 100
          end
     from (select 1 as productID union all select 2 as productID) as p

     if @i % 1000 = 0 print @i

     set @i = @i+1
end
set nocount off
go
set noCount on
declare @i smallint
set @i = 0
while @i < 1000 begin

     insert into dbo.Sales(StoreID, productId, transactionTime, amount)
     select 2,
          p.productID,
          dateAdd(minute, - @i, getDate()),
          case (p.ProductID)
           when 1 then 10
           when 2 then 100
          end
     from (select 1 as productID union all select 2 as productID) as p

     set @i = @i+1
end
set nocount off
go

Finally, we need an index on StoreID and ProductID:

CREATE INDEX [IX_Sales] ON [dbo].[Sales]([StoreID], [productID], [transactionID]) ON [PRIMARY]

GO

We want the script below:

execute dbo.Sales_sel_by_StoreID_ProductID @storeID = 2, @productID = 1

To output this row set: