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.