Database Reference
In-Depth Information
declare
@Errors dbo.tvpErrors
insert into @Errors(RecId, [Error])
values
(11,'Price mistake'),
(42,'Insufficient stock')
exec dbo.TvpDemo @Errors
As you see, you need to mention explicitly that the table-valued parameter is read-only in both, the stored
procedure and the dynamic sql parameter lists.
Table-valued parameters are one of the fastest ways to pass a batch of rows from a client application to a T-SQL
routine. Table-valued parameters are an order of magnitude faster than separate DML statements and, in some cases,
they can even outperform bulk operations.
Now let's run a few tests comparing the performance of inserting the data into the table using different methods
and different batch sizes. As a first step, we create a table to store the data, as shown in Listing 12-14. The actual table
used in the tests has 21 data columns. A few data columns are omitted in the listing in order to save space.
Listing 12-14.
Inserting a batch of rows: Table creation
create table dbo.Data
(
ID int not null,
Col1 varchar(20) not null,
Col2 varchar(20) not null,
/* Seventeen more columns Col3 - Col19*/
Col20 varchar(20) not null,
constraint PK_DataRecords
primary key clustered(ID)
)
The first method we will use to measure performance is to run separate insert statements within the transaction.
The .Net code to do this is shown in Listing 12-15. It is worth mentioning that the only purpose of the code is to
generate dummy data and to test the performance of the different methods that insert the data into the database.
Listing 12-15.
Inserting a batch of rows: Using separate inserts
TruncateTable();
using (SqlConnection conn = GetConnection())
{
/* Generating SqlCommand and parameters */
SqlCommand insertCmd = new SqlCommand(
@"insert into dbo.Data(ID,Col1,Col2,Col3,Col4,Col5,Col6,Col7
,Col8,Col9,Col10,Col11,Col12,Col13,Col14,Col15,Col16,Col17
,Col18,Col19,Col20)
values(@ID,@Col1,@Col2,@Col3,@Col4,@Col5,@Col6,@Col7,@Col8
,@Col9,@Col10,@Col11,@Col12,@Col13,@Col14,@Col15,@Col16,@Col17
,@Col18,@Col19,@Col20)",conn);
insertCmd.Parameters.Add("@ID", SqlDbType.Int);
for (int i = 1; i <= 20; i++)
insertCmd.Parameters.Add("@Col" + i.ToString(), SqlDbType.VarChar, 20);
