主页 / dba / 问题 / 117931
Accepted
Collin Dauphinee
Asked: 2015-10-14 13:51:24 +0800 CST

我可以创建需要输入才能选择的计算列吗？

  • 772

我在一个非常古老且非常大的应用程序中有一个场景，其中我有一个代表一种资源的表：

CREATE TABLE resource (resource_id INT, name NVARCHAR(4000))

该表选自数百个不同的地方，包括应用程序代码中的存储过程和动态 SQL。

一个团队最近更新了此资源的名称以进行本地化，他们的方法非常简单。有一个包含本地化名称的新表，以及resource表上的“默认”语言 ID，用于当名称未针对所请求的语言进行本地化时：

-- Foreign keys omitted
ALTER TABLE resource ADD default_language_id INT
CREATE TABLE resource_local (resource_id INT, language_id INT, name NVARCHAR(4000))

大多数 proc 都有一个@user_language_id参数，所以选择要返回的名称的逻辑很简单：如果存在则resource_local.name匹配，如果language_id = @user_language_id存在则匹配，否则就resource_local.name匹配。language_id = resource.default_language_idresource.name

不幸的是，这会将选择正确名称的逻辑变成如下所示：

SELECT ISNULL(ISNULL(exact.name, default.name), res.name)
FROM resource res
LEFT JOIN resource_local exact ON exact.resource_id = res.resource_id 
    AND exact.language_id = @user_language_id
LEFT JOIN resource_local default ON default.resource_id = res.resource_id
    AND default.language_id = res.default_language_id
WHERE res.resource_id = @resource_id

尝试选择的所有数百个位置resource.name都必须使用此逻辑进行更新，这已将此项目变成整个组织的一项巨大工作，因为每个团队都需要更新其 SQL 以使用此逻辑。这也会导致可维护性问题，因为任何处理此表的新开发人员都需要知道他们不能只使用该name列。

现在为时已晚，但我很好奇：是否有更好的方法来解决这个问题，以便name从中选择列resource只会根据@user_language_id变量（如果存在）“做正确的事”？

sql-server computed-column

2 个回答

  1. Best Answer

    我不确定是否可以这样做，以便resource不需要更改对表的任何引用。似乎需要 a 的事实language_id是所有调用代码都需要注意的根本变化。

    但是，可以将其设计为可以通过以下任一简单方式查询资源的方式。这些选项之一可能是更容易在这么多不同的地方进行和维护的更改。


    表值函数

    使用内联表值函数，我们可以提供以下语法。

    SELECT resource_id, language_id, name
FROM dbo.resourceTVF(@resource_id, @language_id) r

    以下是如何创建函数的示例。它与您的问题本质上是相同的查询，但别名default更改为def(default是 SQL Server 关键字)。

    -- Create the Table-Valued Function
CREATE FUNCTION dbo.resourceTVF (@resource_id INT, @user_language_id INT)
RETURNS TABLE 
AS
RETURN
SELECT @resource_id AS resource_id,
  @user_language_id AS language_id,
  ISNULL(ISNULL(exact.name, def.name), res.name) AS name
FROM dbo.resource res
LEFT JOIN dbo.resource_local exact ON exact.resource_id = res.resource_id 
    AND exact.language_id = @user_language_id
LEFT JOIN dbo.resource_local def ON def.resource_id = res.resource_id
    AND def.language_id = res.default_language_id
WHERE res.resource_id = @resource_id
GO


    看法

    您可以重命名该resource表（例如，至resource_base），然后创建一个resource视图以提供以下 API：

    SELECT resource_id, language_id, name
FROM dbo.resource
WHERE resource_id = @resource_id
  AND language_id = @language_id

    主要缺点是视图定义需要CROSS JOIN所有资源和语言，然后才能将其LEFT JOIN应用于本地和默认资源。即便如此，假设您有适当的索引，这将是一个相当有效的计划，其中包含 4 个单例查找。

    CREATE VIEW dbo.resource WITH SCHEMABINDING AS
SELECT res.resource_id,
  lang.language_id,
  ISNULL(ISNULL(exact.name, def.name), res.name) AS name
FROM dbo.resource_base res
CROSS JOIN dbo.languages lang
LEFT JOIN dbo.resource_local exact ON exact.resource_id = res.resource_id 
    AND exact.language_id = lang.language_id
LEFT JOIN dbo.resource_local def ON def.resource_id = res.resource_id
    AND def.language_id = res.default_language_id
GO


    完整脚本

    这是一个完整的脚本，我在其中实施了这两个建议，加载了少量虚假数据，并运行了一些测试用例。至少对于这些测试用例，两种方法都会产生预期的结果并使用基于循环搜索的计划。

    我认为内联表值函数可能是我首先尝试的方法。请注意，CROSS APPLY如果您一次需要多个资源，则可以使用“加入”表值函数。

    • 4

  2. 我绝对是第二个@Geoff 的内联表值函数 (iTVF) 方法，并且打算自己建议，但他先于我;-)。

    我只想补充一点，似乎有 2 个级别的“默认值”似乎有点令人费解。我的意思是，我不明白为什么要将它default_language_id添加到resource表中。它似乎允许给定页面上的各种资源来自多种语言。我认为如果您只是在新表中拥有基于 locale / LCID 的资源，并且如果找不到，则在resource表中找到默认值，这对最终用户来说更加一致。但是先回到那里只是为了获得默认值language_id？我认为从长远来看，这会导致比解决的问题更多的问题。如果你打算有一个默认值，那么应该有一个默认值。您要么在所需的 LCID 中找到资源名称，要么找不到并退回到默认值（尽管您可能不应该让这种情况发生，因为一个页面同时具有英语（从左到右）和希伯来语或阿拉伯语（从右到左的语言）可能有点混乱；）。

    • 1

相关问题