In Microsoft SQL Server, is it possible to speed up Lag() in some way?

Question

It would seem to be (almost) as easy to scan a table over a clustered index, summarizing a field from the "previous record", as just to summarize that field over the entire table. But no :( Is there anything I can do?

create table #Tmp(n int not null primary key)
insert into #Tmp values(0)

declare @k int = 1

while @k < 1024 * 1024 * 32
begin
    insert into #Tmp
    select n + @k
    from #Tmp

    select @k = @k + @k
end

declare @dummy bigint

declare @d1 datetime = GetDate()

select
    @dummy = Sum(Convert(bigint, n))
from
    #Tmp

declare @d2 datetime = GetDate()

select
    @dummy = Sum(convert(bigint, n0))
from
    (
    select
        n0 = Lag(n) over (order by n)
    from
        #Tmp
    ) as Q

declare @d3 datetime = GetDate()

select Convert(time(3), @d2 - @d1), Convert(time(3), @d3 - @d2)
-- 00:00:01.460, 00:00:46.273

drop table #Tmp

Answer 1

..for sql2019..

select
    @dummy = Sum(convert(bigint, n0)) * (1+APPROX_COUNT_DISTINCT(n0)-APPROX_COUNT_DISTINCT (n0))   
from …

Answer 2

Using SQL server prior to 2019 you can utilise some benefits of Batch Mode by creating a dummy table with a columnstore index.

Running the window function query on SQL Server 2016 it took 25 seconds:

However, after creating the following table

create table dbo.bmode (Id int not null)
create nonclustered columnstore index CC_BatchModeHack on dbo.bmode (Id) 

I can include it in the query using an outer join. It actually plays no part in the query at all however tricks the optimizer into selecting batch mode for the window function

select @dummy = Sum(Convert(bigint, n0))
from (
  select n0 = Lag(n) over (order by n)
  from #Tmp
  left join bmode on 1=0
)q

the result is execution time fell to almost 2 seconds