53

Consider this table: c_const

 code  |  nvalue
 --------------
 1     |  10000
 2     |  20000

and another table t_anytable

 rec_id |  s_id  | n_code
 ---------------------
 2      |  x     | 1

The goal is to have s_id be a computed column, based on this formula:

 rec_id*(select nvalue from c_const where code=ncode)

This produces an error:

Subqueries are not allowed in this context. Only scalar expressions are allowed.

How can I calculate the value for this computed column using another table's column as an input?

p.campbell
  • 98,673
  • 67
  • 256
  • 322
Adnan M. TURKEN
  • 1,554
  • 4
  • 21
  • 36

2 Answers2

77

You could create a user-defined function for this:

CREATE FUNCTION dbo.GetValue(@ncode INT, @recid INT)
RETURNS INT
AS 
   SELECT @recid * nvalue 
   FROM c_const 
   WHERE code = @ncode

and then use that to define your computed column:

ALTER TABLE dbo.YourTable
   ADD NewColumnName AS dbo.GetValue(ncodeValue, recIdValue)
Arulkumar
  • 12,966
  • 14
  • 47
  • 68
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 2
    Will the calculated field be updated whenever the other table is updated? – Salih Erikci Jun 29 '13 at 13:49
  • 2
    @littlestewie: **yes!** Every time some piece of code accesses that `NewColumnName` column, the function will be called and the value will be calculated – marc_s Jun 29 '13 at 13:52
  • 8
    @Binny, marc_s: I wouldn't say a UDF would be as efficient as a view. A view is transparent to the query optimiser while a UDF is essentially a black box. A UDF would have its own execution plan and always run *for every row* affected, whereas a view would blend with the rest of the query and an optimal execution plan would always be produced *for the entire query* that uses the view. – Andriy M Apr 29 '15 at 21:26
  • 1
    Set the IsPersisted property of the calculated field to True in Management studio. Then it's not recalculated every time that field is accessed. Or in DDL, Add PERSISTED after the formula for the field, e.g. ALTER TABLE dbo.YourTable ADD NewColumnName AS dbo.GetValue(ncodeValue, recIdValue) **PERSISTED**; – Reversed Engineer May 08 '17 at 13:45
  • 4
    It's worth noting that you can only use PERSISTED if the value is deterministic - see https://learn.microsoft.com/en-us/sql/relational-databases/user-defined-functions/deterministic-and-nondeterministic-functions for details – DanO Nov 27 '17 at 20:03
  • It's also worth noting that there is a performance hit. My sub-second query went to ~12 seconds when attempting this. '...it will prevent the queries that are submitted on that table from taking benefits of the parallelism...' - Source: https://www.mssqltips.com/sqlservertip/5464/computed-columns-with-scalar-functions-sql-server-performance-issue/ – nesterenes Jul 12 '21 at 20:55
25

This seems to be more of a job for views (indexed views, if you need fast lookups on the computed column):

CREATE VIEW AnyView
WITH SCHEMABINDING
AS

SELECT a.rec_id, a.s_id, a.n_code, a.rec_id * c.nvalue AS foo
FROM AnyTable a
INNER JOIN C_Const c
    ON c.code = a.n_code

This has a subtle difference from the subquery version in that it would return multiple records instead of producing an error if there are multiple results for the join. But that is easily resolved with a UNIQUE constraint on c_const.code (I suspect it's already a PRIMARY KEY).

It's also a lot easier for someone to understand than the subquery version.

You can do it with a subquery and UDF as marc_s has shown, but that's likely to be highly inefficient compared to a simple JOIN, since a scalar UDF will need to be computed row-by-row.

Aaronaught
  • 120,909
  • 25
  • 266
  • 342
  • 1
    Note that an indexed view is not always going to be a "fast lookup" - if the indexed view stores as many rows as the base table (e.g. it is not an aggregation), and it is not a lot skinnier than the base table either, it's not going to be any faster - in cases like that a computed column is a better option than an indexed view IMHO. – Aaron Bertrand Apr 29 '15 at 21:34
  • 1
    ...or if it *is* skinnier than the base table, but you still have to go join against the base table to satisfy the query anyway. The primary advantage to indexed views in my experience has always been the reduced storage of the calculated aggregations, not the removal of the calculation itself. – Aaron Bertrand Apr 29 '15 at 21:38
  • 2
    I'll amend my comment, since I obviously didn't read the entire context here and didn't realize the requirement was to pull a value from a different table. An indexed view would probably be a fine solution in this case, my objection was just about the common misconception (and perpetuation here) that indexed views will always be faster than normal views or computed columns. – Aaron Bertrand Apr 30 '15 at 10:19