而是触发或计算列？哪个更好？ [英] Instead Trigger or Calculated Column? which is better?

问题描述

我想知道下面两种方法之间是否存在性能差异。
基本上，问题是我们在ID中允许使用空格和破折号，但是某些旧版应用程序无法使用它们，因此将其删除。
据我所知，最简单的方法是在触发器中或作为计算列。
SQL如下所示（清理并匿名处理，以防出现错误，请您道歉）
到目前为止，在我们的测试服务器上，这两种方法之间似乎没有任何区别，还有其他人吗有任何输入吗？

I was wondering if there was any performance difference between the two approaches below. Basically, the issue is we allow spaces and dashes in an id but certain legacy applications are unable to use these so they are stripped out. As far as I can see the neatest way to do this is either in a trigger or as a calulated column. The SQL is shown below (cleaned up and anonymized so apologies if an error crept in) So far on our test servers, there doesn't appear to be any difference between the two methods, does anyone else have any input?

[数据库SQL Server 2008]
[查找表20000000行并在增长中]

[Database SQL Server 2008] [Lookup table 20000000 rows and growing]

选项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-使用计算列

Versus Option 2 - use a calculated column

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
)

哪个最好？

推荐答案

计算列将是最佳选择。

Computed columns will be best.

INSTEAD OF 触发器将创建整个插入的伪 首先在 tempdb 中使用表。

The INSTEAD OF trigger will create the whole pseudo inserted table in tempdb first.

对于带有您的 CREATE TABLE 语句的触发器版本（非

For the trigger version with your CREATE TABLE statement (non clustered PK on a heap)

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

而计算的列版本相似，但避免了工作表读取。

Whereas the calculated column version is similar but avoids the worktable reads.

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

您是否有任何理由坚持使用此值？ （而不是使用非持久的计算列）

Is there any reason you are persisting this value at all though? (as opposed to having a non persisted computed column)

这篇关于而是触发或计算列？哪个更好？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！