New Features - SQL 2008: Table-Valued Parameters (TVP)

January 6, 2010 by Christoff Truter SQL  

SQL 2008 introduced a nifty feature called Table-Valued Parameters (TVP) into its codebase. TVPs allow developers to pass sets of data to a stored procedure (i.e. passing a table to a stored procedure parameter).

The first thing we need to do, is define a table type, like this:

CREATE TYPE [dbo].[MyFriends] AS TABLE(
	[firstname] [varchar](50) NOT NULL,
	[lastname] [varchar](50) NOT NULL
)

Create a little table for our testing purposes:

CREATE TABLE [dbo].[friends](
	[friendID] [int] IDENTITY(1,1) NOT NULL,
	[firstname] [varchar](50) NOT NULL,
	[lastname] [varchar](50) NOT NULL,
 CONSTRAINT [PK_friends] PRIMARY KEY CLUSTERED 
(
	[friendID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
	IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, 
	ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Next we create a stored procedure using our newly created table type. Note a TVP must be passed as READONLY - which means you can't modify(insert/update/delete rows) it within the body of the routine you're passing it to.

CREATE PROCEDURE [dbo].[addFriends]
	@friends MyFriends READONLY
AS
BEGIN
	INSERT INTO friends(firstname, lastname)
	SELECT firstname, lastname 
	FROM @friends
END

In our C# snippet we create a DataTable, add some rows to it, and pass it to our stored procedure:

using (SqlConnection connection = new SqlConnection(@"Some connectionString"))
{
    using (SqlCommand command = new SqlCommand("addFriends", connection) { CommandType = CommandType.StoredProcedure })
    {
        DataTable dt = new DataTable();
        
		// Add columns
	dt.Columns.Add("firstname", typeof(String)).MaxLength = 50;
        dt.Columns.Add("lastname", typeof(String)).MaxLength = 50; ;

        // Add some rows
	dt.Rows.Add(new Object[] { "Gerhardt", "Stander" });
        dt.Rows.Add(new Object[] { "Roland", "Cooper" });
        dt.Rows.Add(new Object[] { "Jacques", "Brits" });

        connection.Open();		
        command.Parameters.Add("@friends", SqlDbType.Structured).Value = dt;
        command.ExecuteNonQuery();
    }
}

How does all of this affect performance though?

A few clues can be found at http://technet.microsoft.com/en-us/library/bb510489.aspx

Summarized in the case of bulk inserts vs TVPs; a formatted data file on the server will perform better using a bulk insert, unless it is a complex operation with less than 1000 rows.

Using a remote client process TVPs will usually perform better, unless its a direct insert with more than 1000 rows.