Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Instead Trigger or Calculated Column? which is better?

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?

like image 294
Eoin O Avatar asked Mar 28 '12 16:03

Eoin O


People also ask

Why would you use an instead of trigger?

An INSTEAD OF trigger allows a view, which is not inherently insertable, updatable, or deletable, to be inserted into, updated, or deleted from. See CREATE VIEW for more information about deleteable, updatable, and insertable views.

Are there any disadvantages of using computed column?

Some Limitations. You can not reference columns from other tables for a computed column expression directly. You can not apply insert or update statements on computed columns.

What is true for instead of trigger?

The INSTEAD OF triggers are the DML triggers that are fired instead of the triggering event such as the INSERT, UPDATE or DELETE events. So, when you fire any DML statements such as Insert, Update, and Delete, then on behalf of the DML statement, the instead of trigger is going to execute.

What is the difference between for trigger and instead of trigger?

AFTER trigger fires after a DML operation. INSTEAD OF trigger fires instead of a DML operation. Big difference. INSTEAD OF allows you to override functionality, or implement functionality that otherwise isn't supported.


1 Answers

Computed columns will be best.

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

Plan

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)

like image 62
Martin Smith Avatar answered Oct 19 '22 00:10

Martin Smith