SQL Server's Table-Valued  Parameters are useful, and easy to use, but you should be aware that something that looks loke a table, and seems to behave like a table, can't necessarily be used just like any other table. Leidago gives a developer's guide on how, and why, you should use TVPs in your application.

Microsoft has finally solved the problem of how to send multiple rows of data to a database without having to do it one row at a time. No more looking around for inefficient workarounds or using time consuming methods to meet requirements; Table Value Parameters, or TVPs as they are commonly known, have come to the rescue! In layman’s terms, you can now send a whole bunch of rows in one go.

Why was there a problem? If you are forced to send multiple rows of data from a client application to SQL Server by doing several trips to and from the client application, then your application’s performance was likely to suffer and you wouldn’t be able to easily wrap it all in a single transaction. Sometimes you need to be sure that all alterations to the database are done in one transaction so that, if there is an error, the operation can be entirely rolled back rather than leave the data within the database in an inconsistent state.

Before Table-Valued Parameters were introduced in SQL Server 2008, programmers had to pass multi-row data as serialized, delimited, strings, long parameter lists, or XML. This made both the application and the server more difficult to program reliably. Erland covers all these painful techniques here: Arrays and Lists in SQL Server 2005 and Beyond: When TVPs Do Not Cut it

So what are Table Value Parameters? Table-valued parameters provide an easy way to send. You simply pass a table value parameter through a stored procedure to the server. This feature is available in SQL server 2008 only. Earlier versions of SQL Server do not have this functionality.

How does it work?

Although this recent feature sounds useful, a number of steps to be taken first before you use it. Table-Valued Parameters are declared using user-defined table types. It is not as complicated as it sounds. Firstly, we need to create a table type. Secondly, we create a variable of the table type that we just created and populate it. The variable will actually contain the structure of a database table. Once the table is populated with data, we can manipulate it as we would any other table, until it gets passed as a parameter.

Let’s put this in practice, first let’s create the database and the table.

Our sample database is just going to contain the names of our contacts just do demonstrate the use of TVPs. The code will first create a database and then a table. SQL Server provides SQL Management Studio (SSMS) that makes it simple to create databases and tables by using the Object Browser. I will show both ways of creating a table type for our next step. Firstly, I’ll use SQL to create both the database and table. In SSMS add this query and execute it:

Once your database and table is created, then the next thing we do is to create the table type.

You’ll see that this syntax is pretty close to the CREATE TABLE one. You can define PRIMARY KEY, UNIQUE but not CHECK constraints, you can use IDENTITY and DEFAULT definitions, and you can define computed columns, but there are restrictions.  You cannot use ALTER TABLE statements to change the design of table-valued parameters

If you prefer, you can expand the database then select and expand the Programmability option. Do the same with the Types option and find the User-Defined Table Types options:

Right click on it and select the ‘New User Defined Table Type’ option. The following code should appear in the query window:

This is a template. You need just hit Control Shift M and fill in the form that is then shown. This will generate the same code as above:

After you’ve executed the code, you can verify that the data type has been created by checking in the object Explorer:

Now that we have this table type defined, we have to make it useable. For example if we want to insert data or use any other T-SQL, we have to create a variable of the new table type and populate it. For those familiar with Object Oriented Programming, this is a bit like instantiating a class or object. Once this new variable is created it will inherit the table structure of the new type. This is how it is done:

First of all we declare a variable of table type:

Here’s the result when you run the above code:

The next step is to declare a stored procedure that has a parameter of our new table type that we have defined. We’ll then pass our variable as a parameter to a stored procedure. We’ve already got a regular table that we can populate; the one we created at the beginning, when we created the database. All we need now is to create a stored procedure that will populate that table:

So what does the code above do? Well, first of all, we define the stored procedure by adding our table type contactType then we add a READONLY qualifier. This qualifier is very important and MUST be included when a stored procedure is created. This is mainly because Transact-SQL passes table-valued parameters to routines by reference. Basically, a pointer is passed to the stored procedure and not as we would have expected, the user defined table variable. It therefore isn’t possible to do DML operations that update a table-valued parameter, such as UPDATE, DELETE, or INSERT within a routine. Table-valued parameters are read-only in Transact-SQL code. If you need to modify the data that is passed to a stored procedure or parameterized statement in a table-valued parameter, you will need to insert the data into a temporary table or into a table variable first. It isn’t actually much of a problem to do this, and takes little time.

Now that we have created the code for the stored procedure we can run it. After running it, you should be able to see it in the project explorer as below:

Now all that remains is for us to pass our variable to the stored procedure. It will have to be a stored procedure rather than a function since MSDN says that you cannot pass table-valued parameters to a user-defined function and, anyway, you wouldn’t be able to do an INSERT into a table from within it since DML statements to tables are invalid in scalar UDFs. Lets populate the contact table that we created earlier, add the following code to a query window in SSMS and execute it:

A large part of the code above should be familiar to you. We’ve just added the code to execute the stored procedure.

At which point did we pass the table as a parameter? First we declare a variable of our table type:

At this point our contacts are stored in the @Mtabletype variable. Then we simply pass that variable to the stored procedure that we created:

When I run the query on the base table...

...I get exactly the same result as from the TVP. The physical table is now populated with the data from the TVP! We have demonstrated that it is possible to use a Table-Valued Parameter to send multiple rows of data to a Transact-SQL statement or  a stored procedure  without creating a temporary table, pass an XML variable,  or having the give the procedure a whole lot of parameters.

Why is this useful? It is because we can pass tables from the client application and add them, via a Stored Procedure, to the database, in just one round trip. This is far quicker than any other way of passing multiple rows to the database. We’ll move on to show how this is done, using a simple illustration in VB NET.

Creating a VB.NET Client Application

So far we’ve been using the SQL 2008 SSMS as if it were a client that was interacting with the database. Now we’ll illustrate the same technique from application code. We will be creating a client application using Visual Studio 2008 VB.NET. To start with, make sure that you have dot net framework 3.5 installed. Remember how we created a temporary table to hold our data earlier in the article? That is exactly what we are going to do in our application. Then we will send that data straight to our ‘contacts’ database. First create a windows application and add a button. We will be using stored procedure that we created earlier, so we don’t need an output control on our form. Just a button will do. Double click on the button and add the following code:

In this code, I’ve imported the SQLclient name space. This namespace contains a data type that we need in order to use Table-Valued Parameters. Next we create the temporary table that is going to hold our data. I’ve commented the code very heavily so that it is self explanatory:

We then make a connection to the database:

Next we set the stored procedure that we will be using and add the parameter that we used earlier:

Then we run the stored procedure after adding the table name:

To use TVPs in this way, you have to use System.Data.SqlClient. We’ve show it used with a DataTable, but you can use the technique from a list that is instantiated from the SqlDataRecord class, or from a DbDataReader. As far as I know, none of the major ORMs support this technique, not even Entity Framework. The ODBC driver for SQL Server Native Client allows you to use it but it is best to check out the rather more complex code here Readme_Table-Valued Parameters (ODBC).

Conclusion

Using table valued parameters enables database applications to perform much better than they otherwise would have, by reducing the amount of trips the application makes to the server. You can now just send multiple rows to the server with one call. An OLTP transaction that involves many rows of data can be contained within a single stored procedure call. Without table-valued parameters, you would need to hold the transaction open whilst several calls are made to the server. TVPs are far simpler to process than delimited lists, and there is better type-checking. It is faster, and more efficient than using XML variables to do the same thing.

It isn’t necessarily a panacea in all cases. It certainly makes it simple to pass whole tables of strongly-typed data to the application in one call, where it can be absorbed easily in one transaction without causing the generation of a lot of locks. However, there are restrictions: the data is read-only and, because statistics aren’t generated on TVPs, could be slow to process within the receiving routine if there is a lot of complexity involved. On balance, though, it might very well work for you.

Further reading

Arrays and Lists in SQL Server 2008

Table-Valued Parameters (Database Engine)

Table-Valued Parameters in SQL Server 2008 (ADO.NET)