触发器还是计算列？哪个更好？

Question

我想知道以下两种方法在性能方面是否有区别。 基本上，问题是我们允许ID中包含空格和短横线，但某些旧应用程序无法使用它们，因此这些字符会被删除。 据我所见，最简洁的方法是在触发器或计算列中完成。下面是 SQL 代码（已清理和匿名化，如果出现错误，请谅解）。 到目前为止，在我们的测试服务器上，似乎两种方法没有任何区别，还有其他人有什么意见吗？

[数据库：SQL Server 2008] [查找表：20000000 行且不断增长]

选项1 - 创建触发器

CREATE TRIGGER triMem_Lkup on Mem_Lkup
INSTEAD OF INSERT
AS
BEGIN
  INSERT INTO Mem_lkup
       SELECT ex_id, contact_gid, id_type_code, date_time_created,
              (replace(replace([ex_id],' ',''),'-','')) as ex_id_calc
       FROM inserted
END
GO

对比 选项2 - 使用计算列

CREATE TABLE [dbo].[Mem_lkup](
    [mem_lkup_sid] [int] IDENTITY(1,1) NOT NULL,
    [ex_id] [varchar](18) NOT NULL,
    [contact_gid] [int] NOT NULL,
    [id_type_code] [char] (1) NOT NULL,
    [date_time_created] [datetime] NOT NULL,
    [ex_id_calc]  AS CAST( replace( replace([ex_id],' ','')  ,'-','')  AS varchar(18)) PERSISTED

    CONSTRAINT [PK_Mem_Lkup] PRIMARY KEY NONCLUSTERED 
(
    [mem_lkup_sid] ASC
)

哪一个是最好的？

- Eoin O

---

这个在更新时不会起作用 -- 在更新时，计算会发生变化，但是这个插入触发器不会。 - Hogan

Answer 1

计算列是最好的选择。

INSTEAD OF 触发器会首先在 tempdb 中创建整个伪 inserted 表。

对于使用您的 CREATE TABLE 语句的触发器版本（堆上的非聚集 PK），

SET STATISTICS IO ON;

INSERT INTO [_test].[dbo].[Mem_lkup]
           ([ex_id]
           ,[contact_gid]
           ,[id_type_code]
           ,[date_time_created])
SELECT type AS  [ex_id]
      ,1 [contact_gid]
      ,'A' [id_type_code]
      ,getdate() [date_time_created]
  FROM master..spt_values

给我

Table 'Worktable'. Scan count 0, logical reads 5076
Table 'spt_values'. Scan count 1, logical reads 15

Table 'Mem_lkup'. Scan count 0, logical reads 7549
Table 'Worktable'. Scan count 1, logical reads 15

计算列版本与此类似，但避免了worktable读取。

Table 'Mem_lkup'. Scan count 0, logical reads 7555
Table 'spt_values'. Scan count 1, logical reads 15

你坚持持久化这个值的原因是什么呢？（与非持久化的计算列相比）

- Martin Smith

---

我认为我需要将计算列持久化，因为它作为索引的一部分被使用。(http://msdn.microsoft.com/en-us/library/ms189292.aspx#BKMK_persisted) 这不正确吗？ - Eoin O

@EoinO - 不是的。对于这种情况不适用，只适用于不精确（浮点）值或CLR函数/类型。 - Martin Smith

谢谢@Martin，你的回答真的很有启发性！关于持久化评论，如果你正在处理成千上万条记录，那么这个值会每次都被计算吗？ - Eoin O

@EoinO - 抱歉，不确定为什么我从未回答过你上面的问题。每次选择时，该值都将重新计算。但是，由于不保留它而导致页面上的行数更多，可能会平衡掉这一点。即使持久化，也不能保证不会重新评估。请参见为什么执行计划包括对已持久化的计算列进行用户定义函数调用？ - Martin Smith