Menu
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.
610
While you can't reference another table's column directly within your expression, you can invoke a user-defined function. And therefore, you could create a user-defined function that performs the calculation you need, then simply call that function as your computed column's expression.
Creating Indexes on Persisted Computed ColumnsYou can do this when the column is marked PERSISTED in the CREATE TABLE or ALTER TABLE statement. This means that the Database Engine stores the computed values in the table, and updates them when any other columns on which the computed column depends are updated.
Some LimitationsYou can not reference columns from other tables for a computed column expression directly. You can not apply insert or update statements on computed columns.
A computed column expression can use data from other columns to calculate a value for the column to which it belongs. You can specify an expression for a computed column in SQL Server by using SQL Server Management Studio or Transact-SQL.
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.
164
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
