Computed column based on another computed column?

Question

Hello.

I have a computed column called Cost that returns money.

I want to have another column that returns (Cost * 2), but it doesn't allow me.

Answer 1

So you're doing something like this, and it doesn't work?

select cost, cost*2 as cost2
from table

Answer 2

Computed columns cannot reference other computed columns. Though you ought to be able to just repeat the expression you would like to reference. From MSDN:

A computed column is computed from an expression that can use other columns in the same table. The expression can be a noncomputed column name, constant, function, and any combination of these connected by one or more operators. The expression cannot be a subquery.

I should also add that if this were to work as you would hope, it would present all kinds of new issues you'd have to deal with. Presently, updates across many columns/rows occur in parallel and atomically.

Therefore, it wouldn't make sense to use a computed column in your calculation because it wouldn't exactly have a value...yet. If anything, you'd be using an old, un-updated value.

If you really wanted to avoid duplicating the expression, you could do this in a trigger, though I strongly urge you not do that. Triggers are no fun and should only be used by very savvy people in rare cases.

Answer 3

You have to define it against the base columns in the table.

computed_column_expression Is an expression that defines the value of a computed column. A computed column is a virtual column that is not physically stored in the table, unless the column is marked PERSISTED. The column is computed from an expression that uses other columns in the same table. For example, a computed column can have the definition: cost AS price * qty. The expression can be a noncomputed column name, constant, function, variable, and any combination of these connected by one or more operators. The expression cannot be a subquery or contain alias data types.

Although you could refactor them both to use the same scalar UDF (pass in all the same columns) for easier maintenance and ensure consistency of logic, the performance hit would be huge, and I only use scalar UDFs as a last resort.