Using select fields in other computed fields in SQL Server

Question

I have a table with columns A, B and C, and I want to select from that table, but with some additional fields computed from A, B and C and other computed fields. For example:

• D = A * B + C
• E = 2 * D + 4 * A
• F = D * D * E

I thought I could solve that like this:

select A, B, C, (A * B + C) as D, (2 * D + 4 * A) as E, (D * D * E) as F
from Table

But this results in an error: SQL Server won't let me use D in the expression for E.

I have come up with two ways of working around this:

1. I expand the uses of D in E and F and the uses of D and E in F to their full definitions, e.g. (2 * (A * B + C) + 4 * A) as E, ((A * B + C) * (A * B + C) * (2 * (A * B + C) + 4 * A)) as F

2. Use subqueries like so:

   select A, B, C, D, E, (D * D * E) as F
   from (
       select A, B, C, D, (2 * D + 4 * A) as E
       from (
           select A, B, C, (A * B + C) as D
         ) as UpToD
     ) as UpToE
   

Neither solution is satisfactory: in case 1, the query becomes unmanagably long, and whenever I change the way a field is computed, all fields that depend on it need to be updated. Recursively. In case 2, the situation is a little better, but it still feels awkward to create a subquery for every new field I want to compute and I need to repeatedly list all field names.

What's the best way to express this query?

Thanks in advance!

Answer 1

Although I'm sure some people may frown on it, I'd go with option 2, but reworded like this:

select UpToE.*, (D * D * E) as F
from (
    select UpToD.*, (2 * D + 4 * A) as E
    from (
        select A, B, C, (A * B + C) as D
      ) as UpToD
  ) as UpToE

It's a little cleaner in that you're not repeating the same core columns over and over again. I know some people frown on using the * within a select, but since you are explicitly defining your columns in the actual select from a table (the inner most subquery) you're not going to run into issues when the underlying table changes.

I'm not sure if this would have any performance impact to be aware of though.

Answer 2

I would suggest using functions

CREATE FUNCTION D 
(
    @A int,
    @B int,
    @C int
)
RETURNS int
AS
BEGIN
    RETURN @A + @B + @C
END
GO

create table test (A int null, B int null, C int  null)
insert into test (A,B,C) values(1,2,3)

select A,B,C,dbo.D(1,2,3) as D from test