Thursday, March 29, 2012 11:21 AM
I have to use one computed column to create another computed column on same table.
Please let me know how to do ths same.
Please mark as answer if helpful
Thursday, March 29, 2012 11:28 AM
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
Thursday, March 29, 2012 11:49 AM
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.
Please mark as answer if helpful
- Edited by Amit Srivastava Thursday, March 29, 2012 11:49 AM
Thursday, March 29, 2012 11:51 AM
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
Wednesday, April 04, 2012 2:30 PMModerator
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:
- Marked As Answer by KJian_ Thursday, April 05, 2012 12:47 AM
- Edited by Kalman TothMicrosoft Community Contributor, Moderator Tuesday, October 02, 2012 8:20 PM
Wednesday, April 04, 2012 3:34 PM
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
- Edited by johnqflorida Wednesday, April 04, 2012 3:48 PM Added Cross Apply alternative