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.
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?
@Michael: It is maximum 1000 row values.
@Michael: Good question, thank-you!
@Hardik: Thank-you for the answer!