Instead Trigger or Calculated Column? which is bet

问题:

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?

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

Option 1 - Create trigger

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

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
)

Which one is best?

回答1:

Computed columns will be best.

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

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

Gives me

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)