Getting the Next Row values in Computed Column

Transact-SQL
1

Hi,

Can we get the Next Row value in Computed Column?

We can get it in SQL Statement using LEAD keyword.

Thanks.

2

No. you can't. LEAD() is nondeterministic.

3

Hi

Please find one way of doing this

DROP TABLE #temp 

CREATE TABLE #temp 
  ( 
     val INT NULL 
  ) 

INSERT INTO #temp 
SELECT 1 

INSERT INTO #temp 
SELECT 2 

INSERT INTO #temp 
SELECT 3 

INSERT INTO #temp 
SELECT 4 

INSERT INTO #temp 
SELECT 5 
------------------------------------
SELECT * 
FROM   #temp 
------------------------------------
SELECT val, 
       val + 4                     AS computedColumn, 
       Lead(val + 4) 
         OVER(ORDER BY (SELECT 1)) AS leadComputedColumn 
FROM   #temp 
---------------------------------------

4

Don't think that's what the OP wants. I believe he wants something like"

CREATE TABLE foo(a int, b as (select lead(a) over(...)))

Which is not possible as is. However you can define a table-valued function to do the select and use that.

Haven't tested it but in theory it could do the job.