SQL Server - AFTER INSERT and INSTEAD OF INSERT Trigger Performance

Implementing Oracle BEFORE INSERT Triggers in Microsoft SQL Server

Sometimes you have to use a trigger, not a default value to set the required column value. Consider a simple example:

SQL Server:

  IF OBJECT_ID('sales', 'U') IS NOT NULL
    DROP TABLE sales;
 
  CREATE TABLE sales
  (
     id INT PRIMARY KEY,
     created DATETIME DEFAULT GETDATE()
  );
  GO

Nothing prevents you from inserting any datetime (not the current datetime) to the created column:

  -- You can insert any datetime to created column
  INSERT INTO sales VALUES (1, '2010-09-17');

To enforce the constraint and insert the current datetime to the created column you can use a trigger.

In Oracle, you can use a row-level BEFORE INSERT trigger to set the actual current datetime no matter what value was inserted by a user:

Oracle:

  DROP TABLE sales;
 
  CREATE TABLE sales
  (
     id NUMBER(10) PRIMARY KEY,
     created DATE
  );
 
  -- Define a trigger
  CREATE OR REPLACE TRIGGER tr_sales
    BEFORE INSERT ON sales FOR EACH ROW
  BEGIN
    :new.created := SYSDATE;
  END;
  /

Now even if a user explicitly specifies a datetime, it will be overwritten to the current datetime by the trigger:

Oracle:

  INSERT INTO sales VALUES (1, TO_DATE('2010-09-17', 'YYYY-MM-DD'));
  -- Inserted row: 1, 2012-06-27 (current date)

SQL Server does not provide BEFORE INSERT and FOR EACH ROW triggers, so you have to use either statement-level AFTER INSERT or INSTEAD OF INSERT trigger to set the current datetime.

SQL Server:

  IF OBJECT_ID('sales', 'U') IS NOT NULL
    DROP TABLE sales;
 
  CREATE TABLE sales
  (
     id INT PRIMARY KEY,
     created DATETIME
  );
  GO

AFTER INSERT Trigger

Unlike Oracle, where a row-level BEFORE INSERT trigger was used to set the column value, in SQL Server, you can use a statement-level AFTER INSERT trigger and update the column value after it has been inserted to the table.

So in case of AFTER INSERT trigger, two DML operations are performed: the rows firstly inserted to the table, and then they are updated.

A system inserted table can be used to identify which rows where inserted:

SQL Server:

   CREATE TRIGGER tr_sales ON sales
    AFTER INSERT
    AS
    BEGIN
      UPDATE sales
      SET created = GETDATE()
      FROM inserted
      WHERE sales.id = inserted.id;
    END
   GO

Now similar to Oracle BEFORE INSERT trigger, if a user explicitly specifies a datetime, it will be overwritten to the current datetime by the trigger:

SQL Server:

  INSERT INTO sales VALUES (1, '2010-09-17');
  -- Inserted row: 1, 2012-06-27 (current date)

INSTEAD OF INSERT Trigger

In SQL Server, you can also use a statement-level INSTEAD OF INSERT trigger to overwrite inserted values.

INSTEAD OF INSERT trigger requires one DML operation, you only need to modify values and insert rows, no subsequent update is required:

SQL Server:

  CREATE TRIGGER tr_sales ON sales
    INSTEAD OF INSERT
  AS
  BEGIN
   INSERT sales
   SELECT id, GETDATE()
   FROM inserted;
  END
  GO

This trigger also replaces any value inserted in the created column by a user with GETDATE() function:

SQL Server:

  INSERT INTO sales VALUES (1, '2010-09-17');
  -- Inserted row: 1, 2012-06-27 (current date)

Firstly, let's see how the trigger affects the insert performance in Oracle.

Let's measure the insert performance if you do not use a trigger, and use DEFAULT SYSDATE to set the current datetime to the created column:

Oracle:

  -- Table definition
  DROP TABLE sales;
 
  CREATE TABLE sales
  (
     id NUMBER(10) PRIMARY KEY,
     created DATE DEFAULT SYSDATE
  );

The following PL/SQL block inserts 100,000 rows issuing a commit after each 10,000 row:

Oracle:

  -- Run a loop to insert 100,000 rows
  DECLARE i NUMBER(10) := 1;
                 startd DATE := SYSDATE;
  BEGIN
    WHILE i <= 100000 LOOP
 
       INSERT INTO sales (id) VALUES (i);
       i := i + 1;
 
      -- Commit after each 10,000 row
      IF MOD(i, 10000) = 0 THEN
        COMMIT;
      END IF;
 
      END LOOP;
 
     -- Output the execution time in seconds 
    DBMS_OUTPUT.PUT_LINE((SYSDATE - startd)*24*60*60);
    END;
    /

This PL/SQL block was executed multiple times (dropping and recreating the table each time), and I got the following results:

Now let's define a trigger on the table (remove DEFAULT clause), and measure the insert performance:

Oracle:

  -- Table definition
  DROP TABLE sales;
 
  CREATE TABLE sales
  (
     id NUMBER(10) PRIMARY KEY,
     created DATE
  );
 
  -- Define a trigger
  CREATE OR REPLACE TRIGGER tr_sales
    BEFORE INSERT ON sales FOR EACH ROW
  BEGIN
    :new.created := SYSDATE;
  END;
  /

Using the same PL/SQL block that inserts 100,000 rows committing after each 10,000 row, I got the following results:

You can see that the row-level BEFORE INSERT trigger reduced the insert performance by 40% in Oracle.

Now let's see how the trigger affects the insert performance in SQL Server.

Firstly let's measure the insert performance if you do not use a trigger, and use DEFAULT GETDATE() to set the current datetime to the created column:

SQL Server:

  IF OBJECT_ID('sales', 'U') IS NOT NULL
     DROP TABLE sales;
 
  -- Table definition
  CREATE TABLE sales
  (
     id INT PRIMARY KEY,
     created DATETIME DEFAULT GETDATE()
  );
  GO

The following Transact-SQL block inserts 100,000 rows issuing a commit after each 10,000 row:

SQL Server:

  SET NOCOUNT ON
 
  -- Run a loop to insert 100,000 rows
  DECLARE @i INT = 1,
                  @start DATETIME = GETDATE();
 
  WHILE @i <= 100000 BEGIN
 
    -- Start a transaction
    IF @i % 10000 = 1 
       BEGIN TRANSACTION;
 
     INSERT INTO sales (id) VALUES (@i);
     SET @i = @i + 1;
 
    -- Commit after each 10,000 row
    IF @i % 10000 = 0
      COMMIT;
 
  END
 
  -- Output the execution time in seconds 
  SELECT DATEDIFF(ss, @start, GETDATE()); 
  GO

This Transact-SQL block was executed multiple times (dropping and recreating the table each time), and I got the following results:

Now we will define an AFTER INSERT trigger on the table (removing the DEFAULT clause), and measure the insert performance:

SQL Server:

  IF OBJECT_ID('sales', 'U') IS NOT NULL
     DROP TABLE sales;
 
  -- Table definition
  CREATE TABLE sales
  (
     id INT PRIMARY KEY,
     created DATETIME
  );
  GO
 
  -- Define a trigger
  CREATE TRIGGER tr_sales ON sales
    AFTER INSERT
    AS
    BEGIN
      UPDATE sales
      SET created = GETDATE()
      FROM inserted
      WHERE sales.id = inserted.id;
    END
   GO

Using the same Transact-SQL block that inserts 100,000 rows committing after each 10,000 row, I got the following results:

You can see that the statement-level AFTER INSERT trigger reduced the insert performance by 58% in SQL Server compared with using DEFAULT clause.

Now we will define an INSTEAD OF INSERT trigger on the table (removing the DEFAULT clause), and measure the insert performance again:

SQL Server:

  IF OBJECT_ID('sales', 'U') IS NOT NULL
     DROP TABLE sales;
 
  -- Table definition
  CREATE TABLE sales
  (
     id INT PRIMARY KEY,
     created DATETIME
  );
  GO
 
  -- Define a trigger
  CREATE TRIGGER tr_sales ON sales
    INSTEAD OF INSERT
  AS
  BEGIN
   INSERT sales
   SELECT id, GETDATE()
   FROM inserted;
  END
  GO

Now using the same Transact-SQL block that inserts 100,000 rows committing after each 10,000 row, I got the following results:

You can see that the statement-level INSTEAD OF INSERT trigger reduced the insert performance by 38% in SQL Server compared with using DEFAULT, but it is faster than AFTER INSERT trigger.

We got the following results during the tests:

If you need a trigger to update values in the triggered table (the table on which the trigger is defined), INSTEAD OF INSERT trigger is faster than AFTER INSERT trigger due to the following reasons:

• Before the trigger is fired, SQL Server inserts rows only to inserted table for INSTEAD OF INSERT trigger, and to inserted and the triggered table for AFTER INSERT trigger
• AFTER INSERT trigger requires an additional UPDATE and a join with inserted table

Performance stats is always relative, and the following software/hardware was used for the tests in this article:

SQL Server:

• Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
• Express Edition with Advanced Services on Windows NT 6.1 <X86> (Build 7600:)

Oracle:

• Oracle Database 11g Express Edition Release 11.2.0.2.0 - Beta

System:

• Windows 7 Home Premium, 32-bit
• CPU AMD E-350 1.6 GHz, dual core
• RAM 3 GB, DDR3-1066
• HDD - HITACHI HTS725032A9A364, 320 GB, 7200 rpm, SATA

Also note that all tests were ran multiple times and in different order to get stable results on the system.

• Trigger Conversion from Oracle to SQL Server

• Oracle to SQL Server Migration Knowledge Base
• SQL Server Articles for Oracle DBAs and Developers

Dmitry Tolpeko, dmtolpeko@sqlines.com - July 2012