SQLTeam.com | Weblogs | Forums

Getting the Next Row values in Computed Column

Hi,

Can we get the Next Row value in Computed Column?

We can get it in SQL Statement using LEAD keyword.

Thanks.

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

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 
---------------------------------------

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.