#0159-SQL Server-Row Constructors-Triggers process the entire batch at once


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.

3 thoughts on “#0159-SQL Server-Row Constructors-Triggers process the entire batch at once

  1. Michael Mikic

    Hello Nakul,

    thank you for sharing this interesting fact which was new to me.

    But one more question: is there a row limit (the amount of rows to be inserted) when using row constructors?

    Regards,

    Michael

    Like

    Reply

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s