Performance comparison between using Join and Window function to get lead and lag values
The relatively low row-mode performance of LEAD and LAG window functions compared with self joins is nothing new. For example, Michael Zilberstein wrote about it on SQLblog.com back in 2012. There is quite a bit of overhead in the (repeated) Segment, Sequence Project, Window Spool, and Stream Aggregate plan operators:



In SQL Server 2016, you have a new option, which is to enable batch mode processing for the window aggregates. This requires some sort of columnstore index on the table, even if it is empty. The presence of a columnstore index is currently required for the optimizer to consider batch mode plans. In particular, it enables the much more efficient Window Aggregate batch-mode operator.

To test this in your case, create an empty nonclustered columnstore index:

 -- Empty CS index
CREATE NONCLUSTERED COLUMNSTORE INDEX dummy 
ON dbo.tab1 (id, [time], [value]) 
WHERE id &lt; 0 AND id &gt; 0;


The query:

SELECT
    T1.id,
    T1.[time],
    T1.[value],
    value_lag = 
        LAG(T1.[value]) OVER (
            PARTITION BY T1.id
            ORDER BY T1.[time]),
    value_lead =
        LEAD(T1.[value]) OVER (
            PARTITION BY T1.id
            ORDER BY T1.[time])
FROM dbo.tab1 AS T1;


Should now give an execution plan like:



...which may well execute much faster.

You may need to use an OPTION (MAXDOP 1) or other hint to get the same plan shape when storing the results in a new table. The parallel version of the plan requires a batch mode sort (or possibly two), which may well be a little slower. It rather depends on your hardware.

For more on the Batch Mode Window Aggregate operator, see the following articles by Itzik Ben-Gan:


What You Need to Know about the Batch Mode Window Aggregate Operator in SQL Server 2016: Part 1
What You Need to Know about the Batch Mode Window Aggregate Operator in SQL Server 2016: Part 2
What You Need to Know about the Batch Mode Window Aggregate Operator in SQL Server 2016: Part 3