Using SET as an alternative to Cursors for rolling totals

[Jon Levin]

I had a need to update a column in a table with a calculated value based on the value of the preceeding value in the table (which in turn was based on the preceeding value, etc...).

Although this can easily solved via a cursor, I was able to solve this with a single set statement that first updates a variable (which can be part of a calculation using the 'current' row) and using the knowledge that SET statements are applied 'in order' (top-down).

Most importantly: the technique relies on my observation that the SET statement processes a table in clustered index sequence, or load sequence in a heap table.

I've included a very simple example of this technique below.

I'm curious if anyone is using this (undocumented?) technique and if my assumptions are valid (I'm using this on much larger tables with more complex calculations).

EXAMPLE:

CREATE TABLE #tmp (
num int IDENTITY(1,1) NOT NULL,
val int NULL)

-- load 100 test rows
DECLARE @x int
SET @X = 1
WHILE @X <= 100
BEGIN
INSERT #tmp (val) VALUES (NULL)
SET @X=@X+1
END

DECLARE @i int
SET @i = 1

UPDATE #tmp -- this is a -very- simple example of technique!
SET @i=num+@i, -- @i now has current totaled value + current-row int value
val=@i -- val now stores current subtotal

[X]

You may use select statment instead of set.

Select @var = @var + 1

------------
Jon Levin at 9/4/01 4:24:27 PM

I had a need to update a column in a table with a calculated value based on the value of the preceeding value in the table (which in turn was based on the preceeding value, etc...).

Although this can easily solved via a cursor, I was able to solve this with a single set statement that first updates a variable (which can be part of a calculation using the &#39;current&#39; row) and using the knowledge that SET statements are applied &#39;in order&#39; (top-down).

Most importantly: the technique relies on my observation that the SET statement processes a table in clustered index sequence, or load sequence in a heap table.

I&#39;ve included a very simple example of this technique below.

I&#39;m curious if anyone is using this (undocumented?) technique and if my assumptions are valid (I&#39;m using this on much larger tables with more complex calculations).

EXAMPLE:

CREATE TABLE #tmp (
num int IDENTITY(1,1) NOT NULL,
val int NULL)

-- load 100 test rows
DECLARE @x int
SET @X = 1
WHILE @X <= 100
BEGIN
INSERT #tmp (val) VALUES (NULL)
SET @X=@X+1
END

DECLARE @i int
SET @i = 1

UPDATE #tmp -- this is a -very- simple example of technique!
SET @i=num+@i, -- @i now has current totaled value + current-row int value
val=@i -- val now stores current subtotal