Almost every product comes with a set of predefined system default data, which may or may not be editable by the user. When writing about returning result sets from  triggers, I realized that there are very few product teams who use row constructors (introduced in SQL Server 2008) to populate their default data.

What these teams fail to realize is that many a times, the conventional approach may have a negative impact on the time it takes to insert the data and also reduces the overall efficiency of the entire data load operation. Let’s see a demo.

The Test Setup

To demonstrate the point, I will create a small table in the tempdb database with an INSERT trigger on it that would return us the inserted values from the INSERTED view.

USE tempdb
GO
CREATE TABLE dbo.RowConstructorDemo (RowId INT IDENTITY(1,1),
                                     RowValue VARCHAR(30),
                                     RowDescription VARCHAR(100)
                                    )
GO

CREATE TRIGGER dbo.trig_RowConstructorDemo_Insert
ON dbo.RowConstructorDemo
FOR INSERT
AS
BEGIN
  --Some business logic here
  SELECT ROW_NUMBER() OVER (ORDER BY inserted.RowId) AS [RowNumber],
         inserted.RowId,
         inserted.RowValue,
         inserted.RowDescription
  FROM inserted
END
GO

The Conventional Method

Now, let us try to insert a couple of rows using the conventional style of data insertion, i.e. row-by-row approach.

USE tempdb
GO
--Conventional approach
--Study the number of times the trigger is called
INSERT INTO RowConstructorDemo (RowValue, RowDescription) SELECT 'OS1','Microsoft Windows 2003 Operating System'
INSERT INTO RowConstructorDemo (RowValue, RowDescription) SELECT 'DB1','Microsoft SQL Server 2005'
GO

The INSERT trigger that we have defined on our test table returns us two (2) result sets:

Using Row Constructors

If we use row constructors to insert data, we see that the INSERT trigger returns us only one (1) result set. This is because it is one INSERT statement, and therefore all records are inserted in a single batch.

USE tempdb
GO
--Row Constructor approach
--Study the number of times the trigger is called
INSERT INTO RowConstructorDemo (RowValue, RowDescription)
VALUES ('OS2','Microsoft Windows 2008 R2 Operating System'),
       ('DB2','Microsoft SQL Server 2012')
GO

Comparing Conventional Method v/s Row Constructors

We have 3 INSERT statements as part of the test. They are simple INSERT statements and therefore, when both approaches are submitted together as a single batch, each INSERT statement contributes to approximately 33% of the total cost.

The Conventional approach has 2 INSERT statements, and therefore, the conventional approach takes up 33 * 2 = 66% of the total cost.

On the other hand, the row constructor method has only one INSERT statement, and therefore takes only 33 * 1 = 33% of the total cost.

Please note that the overall cost & performance analysis is dependent upon multiple factors, but overall, row constructors have always been faster than individual inserts for me. They have also been performant and efficient because the subsequent triggers work on batches of rows as opposed to a single row at a time.

Conclusion

If your application/product uses pre-defined static data, I trust that the above post will at the very least inspire you to revisit the individual INSERTs and replace them with row constructors.

Until we meet next time,

Be courteous. Drive responsibly.