Table-valued parameter is another exciting new feature in SQL Server 2008. Essentially, TVP solves the problem of inserting multiple rows into the database. Various solutions have been implemented to achieve this, ranging from round trips for every row to comma separated values. With TVP, you can insert multiple rows with a single round trip to the server without having the additional overheads of composition and de-composition of values.
TVP is not entirely new to SQL Server. SQL Server 2000 introduced the table variables which can be used to store a set of records. Where TVP builds on it, is the ability to pass them as input parameters to stored procedures or functions. This could not be done with table variables.
Let us look at some of the features of TVP before we see how they can be used to insert data.
- TVPs can participate in Set based operations.
- They are strongly typed
- You cannot perform DML operations on TVPs. They can be passed as only READ-ONLY parameters to stored procedures\functions
- Remember that TVPs are materialized in the TempDB. Essentially, it means that if you insert more rows into your TVP, the size of TempDB is what gets affected.
Having seen what TVP is, let us now move onto build an example which will actually use TVP. Here, I will show you a very common scenario that we use for multiple entries. We have an order and each order is comprised of multiple products. I have created the necessary tables using the scripts below
I have also inserted data for the tables above. The next thing to do is to create a user defined type that will insert data into the OrderDetails table.
Now that the type has been created, let me go ahead and create a stored procedure that takes the just created type as an input parameter.
Notice the fact that the TVPs have been declared read only. Also pay attention to the second insert statement. The values are inserted from the input parameter using a single select statement. The only remaining thing to do is to call this stored procedure from the front end.
For this purpose, I have created a data grid where users select the orders and save it into the database. Below is the code that goes into the Save Click routine.
The only thing worth noticing is that I have used a datatable which is quite logical because TVPs are tables anyways. As you can see, the savings in the number of round trips to the database is enormous. The USP of the TVPs lie in the fact that they are not hard to implement at all. Have fun!!!