1:  set nocount on
   2:   
   3:  use tempdb
   4:   
   5:  if (object_id('x') is not null)
   6:      drop table x
   7:  create table x (id int primary key identity(1, 1), date datetime, value bigint, total bigint)
   8:  declare @x table(id int, date datetime, value bigint, total bigint)
   9:   
  10:  declare @d datetime
  11:  declare @i int
  12:  set @i = 1
  13:   
  14:  while (@i < 10000)
  15:  begin
  16:      insert into x (date, value)
  17:          select getdate() + @i, round(rand() * 1000, 0)
  18:      set @i = @i + 1
  19:  end
  20:   
  21:  -- warm up
  22:   
  23:  insert into @x
  24:  select TOP 100
  25:       T1.id
  26:      ,T1.date
  27:      ,(select sum(value) from x)
  28:      ,sum(T2.value) as total
  29:  from x T1 join x T2 on T2.date <= T1.date
  30:  group by T1.id, T1.date, T1.value
  31:  delete from @x
  32:   
  33:  -- technique 1
  34:   
  35:  set @d = getdate()
  36:   
  37:  insert into @x
  38:  select 
  39:       T1.id
  40:      ,T1.date
  41:      ,T1.value
  42:      ,sum(T2.value) as total
  43:  from x T1 join x T2 on T2.date <= T1.date
  44:  group by T1.id, T1.date, T1.value
  45:   
  46:  print replace('Inner Join = {0}ms', '{0}', datediff(ms, @d, getdate()))
  47:  delete from @x
  48:   
  49:  -- technique 2
  50:   
  51:  set @d = getdate()
  52:   
  53:  insert into @x
  54:  select 
  55:       T1.id
  56:      ,T1.date
  57:      ,T1.value
  58:      ,(select sum(T2.value) from x T2 where T2.date <= T1.date) as total
  59:  from x T1 
  60:   
  61:  print replace('Sub Select = {0}ms', '{0}', datediff(ms, @d, getdate()))
  62:  delete from @x
  63:   
  64:  -- technique 3
  65:   
  66:  set @d = getdate()
  67:   
  68:  ;with cte (id, date, value, total) as
  69:  (
  70:      select 
  71:           id
  72:          ,date
  73:          ,value
  74:          ,convert(bigint, null) 
  75:      from x
  76:      where id = 1
  77:      
  78:      union all
  79:      
  80:      select 
  81:           cte.id
  82:          ,cte.date
  83:          ,cte.value
  84:          ,cte.total + x.value
  85:      from cte join x on cte.id = x.id + 1
  86:      where cte.total is null
  87:  )
  88:  insert into @x
  89:  select * from cte
  90:  OPTION (MAXRECURSION 10000);
  91:   
  92:  print replace('CTE = {0}ms', '{0}', datediff(ms, @d, getdate()))
  93:  delete from @x
  94:   
  95:  GO
  96:  if (object_id('x') is not null)
  97:      drop table x