Instead of a trigger or a calculated column? what's better?

Question

I was wondering if there is a performance difference between the two approaches below. Basically, the problem is that we allow spaces and dashes in the identifier, but some legacy applications cannot use them to remove them. As far as I can see, the easiest way to do this is either in the trigger or in the form of a calibration column. The SQL is shown below (cleared and anonymous, so apologies if the error crept in) So far, on our test servers, there is no difference between the two methods, does anyone else have any data?

[SQL Server 2008 Database] [Search Table 20,000,000 Rows and Growth]

Option 1 - Create a 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 Design 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 better?

Answer 1

Computed columns will be the best.

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

For the trigger version with the CREATE TABLE statement (non-clustered PC on the 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 

While the computed version of the column is similar, but avoids reading the worktable .

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

Is there a reason you still keep this value? (as opposed to having an unstable computed column)