Формула для вычисленного столбца на основе столбца таблицы

Рассмотрим эту таблицу: c_const

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

и другая таблица t_anytable

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

Цель состоит в том, чтобы s_id был вычисленным столбцом на основе этой формулы:

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

Это вызывает ошибку:

Подзапросы в этом контексте запрещены. Разрешены только скалярные выражения.

Как я могу вычислить значение для этого вычисленного столбца, используя другой столбец таблицы в качестве ввода?

Ответы

Ответ 1

Для этого вы можете создать определенную пользователем функцию:

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

а затем используйте это для определения вычисленного столбца:

ALTER TABLE dbo.YourTable
   ADD NewColumnName AS dbo.GetValue(ncodeValue, recIdValue)

Ответ 2

Это больше похоже на работу для представлений (индексированные представления, если вам нужен быстрый поиск в вычисленном столбце):

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

Это имеет незначительное отличие от версии подзапроса в том, что он будет возвращать несколько записей вместо создания ошибки, если для объединения есть несколько результатов. Но это легко решить с помощью ограничения UNIQUE на c_const.code (я подозреваю, что это уже есть PRIMARY KEY).

Это также намного легче понять кому-то, чем версия подзапроса.

Вы можете сделать это с помощью подзапроса и UDF, как показала marc_s, но это может быть очень неэффективно по сравнению с простым JOIN, поскольку скалярный UDF должен быть вычислен по строкам.

Ответ 3

Я сравнил план запроса вида с вычисленным столбцом, вызывающим UDF, который использует тот же запрос, что и представление, и был действительно удивлен, обнаружив, что стоимость вычисляемого столбца была намного меньше (8% против 92%). Я использую SQL Server 2016. Кто-нибудь знает, почему это было бы?

Я понял, что вычисляемый столбец будет по существу запрашивать представление для каждой строки родительской таблицы и будет намного медленнее. Я не работаю с таблицей с большим количеством данных, поэтому просто сравниваю планы запросов, а не фактическое время выполнения.