主页 / dba / 问题 / 204545
Accepted
Jersey
Asked: 2018-04-21 08:30:33 +0800 CST

使用函数创建持久计算列

  • 772

我正在与程序员一起开发数据库解决方案。他们希望添加一个计算列来模拟旧查询、过程和系统的旧键并对其进行索引。新键将是 GUIDS。

为此，他们希望为计算列创建一个函数，该函数创建一个值并将其持久化。它不会让他们保留该列。我对这个想法没有任何热情的模糊，我也无法在网上找到任何关于该技术的信息（它是一种技术吗？）。

我认为他们需要添加一个触发器。有没有人有任何想法？

该函数将按以下方式运行：

(SELECT [INT Identity field] FROM TABLE WHERE [GUID COLUMN] = @GUIDKEY

它根据 GUID 返回一个 INT 身份字段。

这将在插入相关表时运行。因此，如果表一持有主键，则相关表二将更新（使用传入的 GUID）以从表一中获取键并将其插入表二。

sql-server sql-server-2008

5 个回答

  1. Best Answer

    仍然不明白为什么这需要成为表中的一列，更不用说持久化的列了。

    为什么不创建一个表值函数，当（且仅当）查询真正需要它时交叉应用？由于旧密钥永远不会改变，因此无论如何都不需要计算或持久化它。

    如果您真的希望旧密钥存在于多个地方（听起来不应该做出这种决定的人已经做出了这种决定），只需在触发器中进行查找并在写入时填充它. 那么它只是表中的一个静态列。

    我仍然强烈推荐一个表值函数来促进这一点，这样您就可以编写触发器以处理多行操作......而无需编写循环或调用标量值函数每一行都重来一遍。

    只是为了展示这些东西实际上有多相似（并质疑“不喜欢它”的首席开发人员）：

    -- bad, slow, painful row-by-row
CREATE FUNCTION dbo.GetIDByGUID
(
  @GuidKey uniqueidentifier
)
RETURNS int
AS
BEGIN
  RETURN (SELECT $IDENTITY FROM dbo.tablename WHERE guid_column = @GuidKey);
END
GO

-- in the trigger:
UPDATE r SET oldkey = dbo.GetIDByGUID(i.guid_column)
  FROM dbo.related AS r
  INNER JOIN inserted AS i
  ON r.guid_column = i.guid_column;

    现在，如果您有一个表值函数，代码非常相似，但是您会发现多行操作的性能要好得多，而单行操作的性能几乎相同。

    -- ah, much better
ALTER FUNCTION dbo.GetIDByGUID_TVF
(
  @GuidKey uniqueidentifier
)
RETURNS TABLE
AS
  RETURN (SELECT id = $IDENTITY FROM dbo.tablename WHERE guid_column = @GuidKey);
GO

-- in the trigger:
UPDATE r SET oldkey = f.id
  FROM dbo.related AS r
  INNER JOIN inserted AS i
  ON r.guid_column = i.guid_column
  CROSS APPLY dbo.GetIDByGUID_TVF(i.guid_column) AS f;
    • 9

  2. 我不确定您为什么认为需要一个函数或计算列来执行此操作。您可以使用默认值向表中添加一个新列，并根据需要对其进行索引。

    CREATE TABLE dbo.whatever ( Id INT );

ALTER TABLE dbo.whatever
ADD YourMom UNIQUEIDENTIFIER
        DEFAULT NEWSEQUENTIALID();

CREATE INDEX ix_whatever ON dbo.whatever (YourMom);

    既然你更新了你的问题，让我们来谈谈这是一个多么糟糕的想法。我将稍微简化一下这个例子。

    CREATE TABLE dbo.whatever ( Id INT PRIMARY KEY);

CREATE TABLE dbo.ennui ( Id INT PRIMARY KEY, meh INT );
GO 

CREATE FUNCTION dbo.BadIdea ( @notguido INT )
RETURNS INT
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS
    BEGIN
        DECLARE @out INT;
        SELECT @out = ( SELECT e.Id FROM dbo.ennui AS e WHERE e.meh = @notguido );
        RETURN @out;
    END;
GO 

ALTER TABLE dbo.whatever ADD ishygddt AS dbo.BadIdea(Id)

/*Will fail*/
ALTER TABLE dbo.whatever ALTER COLUMN ishygddt ADD PERSISTED;

/*Will fail*/
CREATE INDEX ix_whatever ON dbo.whatever (ishygddt);

    如果他们执行数据访问，尝试基于标量函数（即使是具有SCHEMABINDING的确定性函数）持久化计算列将失败。

    消息 4934，级别 16，状态 3，第 23 行表 'whatever' 中的计算列 'ishygddt' 无法持久化，因为该列执行用户或系统数据访问。

    你也不能索引它：

    消息 2709，级别 16，状态 1，第 25 行表 'dbo.whatever' 中的列 'ishygddt' 不能用于索引或统计信息或作为分区键，因为它执行用户或系统数据访问。

    您还会遇到很多问题，因为该函数将逐行运行以检索数据，并将强制针对表的所有查询以串行方式运行。

    如果您正在修改函数引用的表中的数据，并从调用计算列中的函数的表中选择数据，您最终可能会遇到一些非常令人困惑的阻塞场景，其中函数被阻止将数据返回到查询在看似无关的桌子上。

    这是一个坏主意。亚伦在他的评论中给出了我认为最好的建议：

    为什么不创建一个表值函数，当（且仅当）查询真正需要它时交叉应用？由于旧密钥永远不会改变，因此无论如何都不需要计算或持久化它。如果您真的希望旧密钥存在于多个地方，只需在触发器中进行查找。

    • 8

  3. 您可以阅读BOL 中的 Persisted Computed Columns以及 Computed Columns 上的相关索引。

    可以在持久计算列中使用的表达式有一些限制。表达式“必须在指定 PERSISTED 时具有确定性”。

    • 5

  4. 如果我理解正确，您有：

    1. 一张桌子，我们称之为t。
    2. Tablet有一个 guid 列，我们称之为gc。
    3. 附加表是将值映射t.gc到不同类型的不同键值的查找，用于遗留代码。让我们调用 tablelt和 legacy key column lk。
    4. 您希望lt.lk以这样的方式出现，t以便t.lk可以继续使用它的遗留代码。

    我会使用视图进行调查。

    1. 重命名t，类似t_base.
    2. 创建一个名为的视图，该视图t连接并t_base返回和lt的列。t_baselt.lk
    3. 如果需要持久化，请调查索引视图。（需要企业版并且有很多限制，但可能适合这个加入。）
    • 1

  5. 实际问题

    开发人员试图解决的问题是一个相当标准的迁移问题，从一种数据类型到一种新数据类型。他们有一个在最初设计数据库时没有预料到的新问题；也就是说，他们现在需要跨数据库同步数据。这往往是一项代价高昂的工作，尤其是在大量代码中依赖数据类型的情况下。寻找成本节约措施并非完全不合理。

    他们的解决方案

    数学说不

    首先，重要的是要认识到这个问题在数学上可能是难以解决的。GUID 或 UUID 只是一个 128 位或 16 字节的数字。列的大小IDENTITY取决于使用的数据类型，但通常是INT（32 位，4 字节）；有时BIGINT使用（64 位，8 字节）。没有数学方法可以将 GUID 的范围完全映射到 INT 甚至 BIGINT 的范围。如果该列DECIMAL(38,0)足够大，则在数学上是可能的，但这种情况非常罕见。

    实用性

    即使可以将 GUID 映射到DECIMAL类型，但这并不意味着它是实用的。几乎没有人这样做，因此您将不得不花费时间（=金钱）确保映射正常工作。他们的解决方案引入了一个不小的风险，即产生奇怪且难以诊断的错误。

    此外，您不会使用他们的解决方案保留数据的现有 ID。这很可能会破坏最终用户拥有的任何包含 ID 的书签。

    最后，他们的解决方案很可能是根深蒂固的。由于上述所有原因，这不是一个好习惯，但如果他们得到了它，他们很可能不会很快摆脱它，因为在所有新的情况下继续使用整数键太“容易”了代码。

    更标准的方法

    将同步引入现有系统的一种相对标准的方法是添加一个新的唯一 ID。除了旧的现有键之外，此新 ID 还放置在数据中。然后代理键不同步。

    这有一些主要好处：

    • 解决了启用跨数据库同步的问题。
    • 依赖旧密钥的现有代码不必更改。（绝对不会在短期内，也可能永远不会。）
    • 没有数学上不可能的映射。
    • 现有的键值被保留。

    这种方法有两个小烦恼：

    • 由于代理键不同步，如果代理键出现在代码中，特别是在应用程序中，那么这些 ID 在不同的数据库中将是不同的。对于您的特定情况（同步以测试和开发数据库的副本，而不是跨多个生产数据库的某种复制），这只是一个小麻烦。然而，这也是一个可以解决的问题：随着这种需求变得更加明显，无论这会造成什么效率低下，开发人员都可以调整特定的、有针对性的代码片段，以根据需要使用新的同步 ID，而无需重写整个应用程序。他们甚至可能在一段时间内同时支持这两个 ID。（例如，Web 端点可能会接受一个整数键，然后重定向到 GUID 键以确保书签不会损坏。）这也成为了在代码中逐渐放弃使用整数键的动机。
    • 在使用外键同步数据时，同步代码可能必须映射整数代理 ID。不过，这远非一个棘手的问题。您只需查找相关的同步 ID 并使用它来查找目标数据库的整数代理键。但是，听起来您的开发团队已经准备好将外键从现有的代理键切换到新的 GUID 键，所以这可能根本不是问题。

    不过，这两个问题都是可以管理的，如果现在将所有内容都切换到 GUID 成本太高，那么它们是合理的权衡取舍。

    还值得注意的是，此解决方案完全启用了他们要求能够执行的查询。

    现在可能为时已晚，无法为您提供帮助，但我认为这是很好的信息。

    • 1

相关问题