Computed Column Specification

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

Answer 1

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

follow the second method only....

Answer 2

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

Answer 3

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