locked
Computed Column Specification RRS feed

  • Question

  • Can a formula for a computed column specification REFERENCE another computed column in the same table?

    eg:

    ComputedCol1 = ColA * ColB

    ComputedCol2 = ComputedCol1 / 2

     

    or

    does ComputedCol2 need to be:  (ColA * ColB) /2

    Saturday, August 21, 2010 5:14 PM

Answers

  • No.... You should not refer another computed column in calculating one computed column specification...

    follow the second method only....

    • Marked as answer by mtpaper Saturday, August 21, 2010 8:24 PM
    Saturday, August 21, 2010 5:23 PM
  • declare @t table (ColA int, ColB int, ComputedCol1 as ( ColA * ColB ), 
    ComputedCol2 as (ColA * ColB) /2)
    insert into @t
    select 4, 5
    
    select * from @t

    http://www.t-sql.ru
    • Proposed as answer by Naomi N Sunday, August 22, 2010 3:28 AM
    • Marked as answer by Kalman Toth Sunday, August 22, 2010 3:35 PM
    Saturday, August 21, 2010 5:24 PM

All replies

  • No.... You should not refer another computed column in calculating one computed column specification...

    follow the second method only....

    • Marked as answer by mtpaper Saturday, August 21, 2010 8:24 PM
    Saturday, August 21, 2010 5:23 PM
  • declare @t table (ColA int, ColB int, ComputedCol1 as ( ColA * ColB ), 
    ComputedCol2 as (ColA * ColB) /2)
    insert into @t
    select 4, 5
    
    select * from @t

    http://www.t-sql.ru
    • Proposed as answer by Naomi N Sunday, August 22, 2010 3:28 AM
    • Marked as answer by Kalman Toth Sunday, August 22, 2010 3:35 PM
    Saturday, August 21, 2010 5:24 PM
  • Can a formula for a computed column specification REFERENCE another computed column in the same table?


    You get an error message when you try to do that.

    declare @t table (
    ColA int, 
    ColB int, 
    ComputedCol1 as ( ColA * ColB ), 
    ComputedCol2 as (ComputedCol1) /2)
    /*
    Msg 1759, Level 16, State 0, Line 1
    Computed column 'ComputedCol1' in table '@t' is not allowed to be used 
    in another computed-column definition.
    */
    

    However, you can achieve your goal with the application of UDF as computed columns:

    -- SQL Server: using UDF for computed column(s)
    create function fnColAxColB ( @ID int) 
    returns int
    as
    begin
    return (select ColA * ColB from test where ID = @ID )
    end;
    GO
    
    create table test (
    ID int identity (1,1) primary key,
    ColA int, 
    ColB int, 
    ComputedCol1 as (dbo.fnColAxColB(ID) ), 
    ComputedCol2 as (dbo.fnColAxColB(ID)) /2)
    GO
    insert test(ColA,ColB) VALUES
    (5,7), (11,3), (8,64)
    select * from test
    /*
    ID	ColA	ColB	ComputedCol1	ComputedCol2
    1	5	7	35	17
    2	11	3	33	16
    3	8	64	512	256
    */
    

    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Sunday, August 22, 2010 3:34 PM