基于不同表的列的计算列公式

Question

考虑这个表格：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)

这会产生一个错误：

在此上下文中不允许子查询。只允许标量表达式。

我该如何使用另一个表格的列作为输入来计算这个计算列的值？

- Adnan M. TURKEN

Answer 1

您可以为此创建一个用户定义的函数：

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

然后使用它来定义你的计算列：

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

- marc_s

---

当其他表更新时，计算字段是否会被更新？ - Salih Erikci

@littlestewie: 是的！ 每当某段代码访问NewColumnName列时，该函数将被调用并计算出相应的值。 - marc_s

@Binny, marc_s: 我不会说一个用户自定义函数（UDF）和一个视图一样高效。一个视图对查询优化器来说是透明的，而一个UDF本质上是一个黑匣子。一个UDF将有其自己的执行计划，并始终运行对于每一行受影响的数据，而一个视图将与查询的其余部分融合，并且一个最优的执行计划将始终被生成用于使用该视图的整个查询。 - Andriy M

在管理工作室中将计算字段的IsPersisted属性设置为True。然后，每次访问该字段时就不会重新计算它。或者在DDL中，在字段公式后添加PERSISTED，例如：ALTER TABLE dbo.YourTable ADD NewColumnName AS dbo.GetValue(ncodeValue, recIdValue) PERSISTED; - Reversed Engineer

值得注意的是，只有当值是确定性的时候，才能使用PERSISTED - 有关详细信息，请参见https://learn.microsoft.com/en-us/sql/relational-databases/user-defined-functions/deterministic-and-nondeterministic-functions。 - DanO

值得注意的是，这样做会影响性能。当我尝试这样做时，我的子秒查询时间从原来的不到1秒变成了约12秒。同时，它还会阻止在该表上提交的查询利用并行处理的优势。 - nesterenes

Answer 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约束来解决（我怀疑它已经是一个PRIMARY KEY）。此外，它比子查询版本更容易理解。

你可以像marc_s所展示的那样使用子查询和UDF完成它，但是相比于简单的JOIN，这可能会非常低效，因为标量UDF需要逐行计算。

- Aaronaught

---

请注意，索引视图并不总是会成为“快速查找” - 如果索引视图存储的行数与基表一样多（例如，它不是一个聚合），并且它也没有比基表更窄，那么它就不会更快 - 在这种情况下，计算列比索引视图更好，以我个人的看法。 - Aaron Bertrand

如果索引视图比基础表更小，但您仍然必须加入基础表以满足查询，则可以使用它。根据我的经验，索引视图的主要优点始终是减少计算聚合的存储空间，而不是删除计算本身。 - Aaron Bertrand

我会修改我的评论，因为我显然没有阅读完整个上下文，并且没有意识到要从不同的表中提取值的要求。在这种情况下，索引视图可能是一个很好的解决方案，我的反对只是关于常见误解（并在此处持续）索引视图总是比普通视图或计算列更快。 - Aaron Bertrand