While I was developing my trading algo, I wanted to calculate different technical indicators, most of which depend on previous data points. In order to do that in SQL Server you have a couple options, and this is one of them.

There are two key points that make this “quirky update” work.

You MUST have a clustered index on the table that represents the expected sort order of the data. In this case is was the tick data ascending.
You MUST use OPTION (MAXDOP 1) option on the query so SQL Server doesn’t try to parallelize the update.

Setup#

Below we create a staging table to house the technical indicators, the primary key is the clustered index, and tick data is inserted into the table in sorted order. You could also have the clustered index on the [Time] column as well.

1
--DROP TABLE [stage].[Meta]
2
CREATE TABLE [stage].[Meta] (
3
  [RowIdx] BIGINT IDENTITY PRIMARY KEY,
4
  [Id] UNIQUEIDENTIFIER,
5
  [InstrumentId] INT,
6
  [Pip] NUMERIC(10, 5),
7
  [Time] DATETIME2,
8
  [Open] NUMERIC(10, 5),
9
  [High] NUMERIC(10, 5),
10
  [Low] NUMERIC(10, 5),
11
  [Close] NUMERIC(10, 5),
12
  [Volume] INT
13
);

In the following examples take note of the usage of the variables that retain the previous calculation result and how they get used when updating the calculation for the next row. This essentially causes the same behavior you would get if you had used a SQL CURSOR.

Example — Heikin Ashi Candlestick#

1
/* indicator columns
2
ALTER TABLE [stage].[Meta]
3
ADD  [HaOpen] NUMERIC(10, 5),
4
    [HaHigh] NUMERIC(10, 5),
5
    [HaLow] NUMERIC(10, 5),
6
    [HaClose] NUMERIC(10, 5)
7
--*/
8

9
-- calculate heikin ashi candles
10
-- NOTE: variable ordering matters here
11
---------------------------------------------------------------------------------------------------
12
DECLARE
13
    @HaOpen NUMERIC(10, 5) = 0,
14
    @HaClose NUMERIC(10, 5) = 0,
15
    @PrevHaOpen NUMERIC(10, 5) = 0,
16
    @PrevHaClose NUMERIC(10, 5) = 0
17

18
-- quirky update --
19
UPDATE [stage].[Meta]
20
SET @HaClose = ([Open] + [High] + [Low] + [Close]) / 4.0,
21
    [HaClose] = @HaClose,
22

23
    @HaOpen = CASE WHEN [RowIdx] = 1 THEN ([Open] + [Close]) / 2.0 ELSE (@PrevHaOpen + @PrevHaClose) / 2.0 END,
24
    [HaOpen] = @HaOpen,
25

26
    [HaHigh] = (SELECT MAX([val]) FROM ( VALUES ([High]), ([HaOpen]), ([HaClose]) ) T([val])),
27
    [HaLow] = (SELECT MIN([val]) FROM ( VALUES ([Low]), ([HaOpen]), ([HaClose]) ) T([val])),
28

29
    @PrevHaClose = @HaClose,
30
    @PrevHaOpen = @HaOpen
31
OPTION (MAXDOP 1);

Example — Exponential Moving Average#

1
/* indicator columns
2
ALTER TABLE [stage].[Meta]
3
ADD [EMA012] NUMERIC(10, 5),
4
    [EMA026] NUMERIC(10, 5)
5
--*/
6

7
-- calculate ema's
8
-- NOTE: depends on sma being calculated first
9
---------------------------------------------------------------------------------------------------
10
-- quirky update --
11
DECLARE
12
    @EMA012 NUMERIC(10, 5) = 0,
13
    @EMA026 NUMERIC(10, 5) = 0
14

15
UPDATE [stage].[Meta]
16
SET @EMA012 = CASE WHEN [RowIdx] = 1 THEN [SMA012] ELSE ([Close] - @EMA012) * (2 / ((12 + 1) * 1.0)) + @EMA012 END,
17
    [EMA012] = @EMA012,
18

19
    @EMA026 = CASE WHEN [RowIdx] = 1 THEN [SMA026] ELSE ([Close] - @EMA026) * (2 / ((26 + 1) * 1.0)) + @EMA026 END,
20
    [EMA026] = @EMA026
21
OPTION (MAXDOP 1);

Summary#

I first came across this when looking for a better way to perform the update without having all of the ceremony that comes with the CURSOR. You can find the original article below and their 10 rules on using this technique.

The Rules & Inspiration

CLUSTERED INDEX MUST BE PRESENT IN THE CORRECT ORDER
PARALLELISM MUST BE PREVENTED: MUST include OPTION (MAXDOP 1)
DON’T WORK AGAINST PARTITIONED STRUCTURES
USE THE TABLOCKX HINT
DO NOT USE JOINS
YOU MUST HAVE AN “ANCHOR” COLUMN
DO NOT USE ORDER BY
DO NOT USE INDEX HINTS TO TRY TO FORCE ORDER
GET IT RIGHT
TEST!

~ SK