New Features - SQL 2008: Table-Valued Parameters (TVP)
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[] { "Eugene", "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.
Posted by - Christoff Truter
Date - 2010-01-06 22:58:25
Post comment