How to use one computed column in another computed column.

Question

---

Hi All,

I have to use one computed column to create another computed column on same table.

Please let me know how to do ths same.

Amit
Please mark as answer if helpful
http://fascinatingsql.wordpress.com/

Answer 1

You can't but see below to get an idea for a workaround

declare @t table(id int,id1 as id/2,id2 as (id/2)/2)

---

Thanks and regards, Rishabh , Microsoft Community Contributor

Answer 2

Hi Risabh,

Thank for your quick reply.

But i have to perform quite a bulky calculation on each and every computed column, hence computed column is desperatly required for other computed column.

---

Amit
Please mark as answer if helpful
http://fascinatingsql.wordpress.com/

Answer 3

Chaining of computed columns in not allowed.

Another way you can achieve this is by creating a view on the table:

CREATE TABLE ComputedColumnTest
(
	Col1 INT,
	Col2 INT,
	Col3 AS Col1 + Col2
)	

CREATE View viewComputedColumnTest
AS
SELECT Col1, Col2, Col3, Col4 = Col3 * 2
FROM	ComputedColumnTest

---

- Vishal

SqlAndMe.com

Answer 4

CREATE TABLE ComputedColumnTest
(
	Col1 INT,
	Col2 INT,
	Col3 AS Col1 + Col2,
	Col4 AS Col3 + Col1
)	
/*
Msg 1759, Level 16, State 0, Line 1
Computed column 'Col3' in table 'ComputedColumnTest' is not 
allowed to be used in another computed-column definition.
*/

Best you can do is to post a "wish" at Connect.

Computed column article:

http://www.sqlusa.com/bestpractices2005/design/computedcolumn/

---

Kalman Toth SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

Answer 5

Although every situation is different, "Wrapper" style outer queries surrounding an inner Derived query that do nothing but add computed columns often have few if any performance sacrifices. And if you had to do a complex where clause and join, etc., you could put it at the innermost of the derived queries, reducing performance potential problems as much as possible.

So for example, an oversimplified example:  A total of 4 derived queries, each derived queries parent creating a new computed column, and feeding upward.

Select A4 *2 as A5, * From
 (Select A3 * 2 as A4, * from
   (Select A2 * 2 as A3, * from
     (Select A1 * 2 as A2, * from
       (Select 1 as A1) as DQ1
      ) as DQ2
    ) as DQ3
  ) as DQ4

Some people also use CROSS APPLY to do somewhat similar things too.  When possible, I think the derived query approach is easier to read, though. 

EDITED: Or should I say, "More universally understood", rather than easier to read.  Here's a CROSS APPLY approach, which is shorter.  The catch is, Cross Apply isn't well understood, so your future maintenance programmer who takes over won't understand it, and will say, "That no good Amit always did things in a confusing way... "  (Humor  :-)

Wrapper_style: -- Wordier
		Select A4 *2 as A5, * From
		 (Select A3 * 2 as A4, * from
		   (Select A2 * 2 as A3, * from
			 (Select A1 * 2 as A2, * from
			   (Select 1 as A1 UNION Select 2 UNION select 3) as DQ1
			  ) as DQ2
			) as DQ3
		  ) as DQ4
 
Cross_apply: -- Shorter, but CROSS APPLY confuses more people 
		 Select *
		  From (Select 1 as A1 UNION Select 2 UNION select 3) as D
		  Cross Apply (Select D.A1 * 2 as A2) D2
		  Cross Apply (Select D2.A2 * 2 as A3) D3
		  Cross Apply (Select D3.A3 * 2 as A4) D4
		  Cross Apply (Select D4.A4 * 2 as A5) D5