Part 1: Table Valued Parameters (SQL Server 2008+)

This blog post only exists to prevent expired linking. My blog has moved to: http://pabloaizpiri.com/

--------------------------------------------------------------------------------

At work we're making an effort to contribute technical knowledge to a centralized IT wiki. I like that. Writing encouraged a good understanding of the technology and it's benefits. I didn't think I'd have much to write but I'm surprised how some things I take for granted as simple, others didn't know and vice versa. This two part series is from those entries.


Introduction
Normally, inserting rows into SQL server is quick and easy and done through a simple INSERT SQL statement. This is fine when saving data to one, two, or even a few rows. However, when it is necessary to insert larger sets of data, an array, or some kind of dynamic list atomically, this method becomes not only functionally inadequate, but slow and clunky. This may also be the case when updating a large number of records individually. In this entry (part one of a two-part series) I wanted to write about the first of two options we will look at for inserting/updating larger sets of data: Table Valued Parameters.

  

Using Table Valued Parameters in SQL Server 2008
When it is necessary to insert or update more than just a few rows or a list of data, a TVP (table valued parameter) is the next step. TVP’s became available in SQL Server 2008 and are perfect for sending a stored procedure a dataset of anywhere from a few rows to around a thousand.

To demonstrate, I am going to use some samples from a recent project that I updated to use TVPs. In order to create a stored procedure that can take a table as a parameter, first we create a UDT (User Defined Type) of the table type. You create your user defined table type (UDTT) just like you would normally create a SQL server table:

CREATE TYPE [dbo].[typProjectStreamLight] AS TABLE(
[PCID] [int] NOT NULL,
[Stream] [varchar](100) NOT NULL,
[StreamType] [varchar](20) NOT NULL,
[BudgetMonthLabel] [varchar](20) NOT NULL,
[Value] [decimal](18, 6) NULL
)

GO

Once you have created the UDTT, you can create a stored procedure that will take the UDT as a parameter:

-- =============================================
CREATE PROCEDURE [dbo].[spProjectStreams_AllMonths_set]
@CID INT,
@UserName VARCHAR(25),
@tvpMonthValueList typProjectStreamLight READONLY
AS
BEGIN

 In this example, the parameter is named “@tvpMonthValueList” and you can see the UDT as the declared data type next to the parameter name. (“typProjectStreamLight”) Note that to use UDTT’s as arguments you must declare them as read-only.

Here’s another sample using TVP’s as a way to emulate a list of data or an array. Here’s the UDTT declaration:

CREATE TYPE [dbo].[typIntList] AS TABLE(
[Value] [int] NOT NULL
)
GO

And here the procedure using it as an argument:

CREATE PROCEDURE [dbo].[spUpdateCalculatedStreamsFromList]
@PCIDList [typIntList] READONLY,
@LastUpdatedBy VARCHAR(25)
AS
BEGIN


Communicating between .NET and SQL Server 2008 using TVPs
Using a stored procedure with .NET is very simple- the main difference is a SQL Data Type of “Structured” is assigned to the parameter containing the table. Because the System.Data.SqlClient supports populating table-valued parameters from DataTable, DbDataReader or IEnumerable objects, populating the UDTT is very easy. Below is an example with the generic setup for the SQL command same as always plus our new table:

Dim objConnection As SqlConnection = DataAccess.modConnection.GetSQLConnection
objConnection.Open()
Using objCommand As SqlCommand = objConnection.CreateCommand()
With objCommand
.CommandType = CommandType.StoredProcedure
.CommandText = "spProjectStreams_AllMonths_set"
.Parameters.AddWithValue("@CID", CID)
.Parameters.AddWithValue("@UserName", DBNullIF(UserName))
.Parameters.AddWithValue("@tvpMonthValueList", mrshlTable)
.Parameters("@tvpMonthValueList").SqlDbType = SqlDbType.Structured.ExecuteNonQuery()
End With
End Using

In the sample above, “mrshTable” is a DataTable object that matched the schema of the “typProjectStreamLight” UDTT described previously.

Considerations when using TVP’s
There are a couple of things to keep in mind when using UDTT’s: (At least as of July 2011)

• As noted above, UDTTs must be declared as read-only when used as an argument in a stored procedure- this means you won’t be able to modify the data in the UDTT.
• UDTT’s cannot be passed to a SQL function.
• UDTT’s cannot be modified after created- they must be dropped and created again.
• UDTT’s cannot be dropped if there are any stored procedures currently using them as arguments. First the stored procedure must be dropped or altered to not use the UDTT and then the UDTT may be dropped.

Here are some benefits you may reap by using TVP’s correctly:

• Update/Insert operations can be performed with less network overhead.
• Using a UDTT in a stored procedure allows performing your INSERT/UPDATE operations in a single set allowing for a faster, more efficient SQL Server performance
• By using a stored procedure that accepts a UDTT, using one connection, running the whole operation in a transaction, and staying away from ad-hoc SQL become much easier and the default.
• I’ve found it may be much easier to write/refactor code in .NET to use a table and simply pass it to the stored procedure when we are ready to insert/update the data.