lag_trick.sql

declare @i varchar(max)='1'+replicate(',1',3999),@d datetime2 = sysdatetime(),@_ int
--slow
select @_=i.value('.','int')
from (
    select a=convert(xml,'<i>'+replace(@i,',','</i><i>')+'</i>')
) a
cross apply a.nodes('./i') x(i)
select datediff(ms,@d,sysdatetime());
select @d=sysdatetime();
--fast
select @_=i.value('.','int')
from (
    select a=lag(convert(xml,'<i>'+replace(@i,',','</i><i>')+'</i>'),0) over (order by (select 1))
) a
cross apply a.nodes('./i') x(i)
select datediff(ms,@d,sysdatetime());