The most efficient way to join huge tables
update a1
set  a1.value_last = LAG(value, 1,0)  OVER (partition by id ORDER BY period)
  ,  a1.value_next = LEAD(value, 1,0) OVER (partition by id ORDER BY period)
from tab1 a1


index on id, period  

or just use a view - that might surprise you 

CREATE VIEW tab1LastNext  
AS  
select a1.id, a1.period, a1.value
     , LAG(value, 1,0)  OVER (partition by id ORDER BY period) as value_last
     , LEAD(value, 1,0) OVER (partition by id ORDER BY period) as value_next
from tab1 a1;


for logging
need to hope value is not null or this gets messy  

select 1;
while @@rowcount &gt; 0
begin 
  update top (10000) a1
  set a1.value_last = LAG(value, 1,0) OVER (partition by id ORDER BY period)
  from tab1 a1 
  where LAG(value, 1,0) OVER (partition by id ORDER BY period) is not null 
    and LAG(value, 1,0) OVER (partition by id ORDER BY period) != a1.value_last
end 
select 1;
while @@rowcount &gt; 0
begin 
  update top (10000) a1
  set a1.value_next = LEAD(value, 1,0) OVER (partition by id ORDER BY period)
  from tab1 a1 
  where LEAD(value, 1,0) OVER (partition by id ORDER BY period) is not null 
    and LEAD(value, 1,0) OVER (partition by id ORDER BY period) != a1.value_next
end