Table Valued Parameters – SQL Server 2008

Today we will be discussing on an interesting enhancement to SQL Server 2008 i.e. table valued parameters.

What is a Table Valued parameter (TVPs)?

TVPs are introduced in SQL Server 2008 which gives us another choice (Remember CTEs, Temp tables and table variables) of treating a set of rows as a separate entity that you can query or join against. It is a user defined table type in SQL Server 2008 that describes the schema for set of rows which can be passed easily to a user defined stored procedure or functions.

Comparison between a CTE, table variable, temp variable and table valued parameter:

• CTE and table variable store their data in memory within the allocated RAM cache and if it exceeds then they get pushed to tempDB where as temp table and TVP are always stored in tempDB. This means a performance hit when using smaller number of rows but a performance gain while dealing with large number of rows. 
• TVPs can be indexed where the indexing is limited to Primary keys and unique constraints where as CTEs cannot. 
• The main advantage of TVP is the ability to pass on the entire set of records to stored procedures and functions. Table variables, temp tables and CTEs on the other hand can not. 
• TVPs are also highly reusable. The schema is centrally maintained which can be defined only once and can be reused again and again.

 Syntax for creating a TVP:

In the above code snippet we created a new TVP or user defined table type called EmployeeTVP with three columns i.e. EmpID, EmpName and Department.

Now we can create variables of type EmployeeTVP that can hold data that fits this schema. It will be stored in tempDB and can be passed freely to stored procedures and functions. If the declared variables fall out of scope or are no longer referenced then SQL server deletes the underlying data in tempDB.

We can view the TVP in the SQL server management studio under DB > Programmability > Types > User-Defined Table Types. Below is the screenshot.

 

Syntax for declaring a parameter of type EmployeTVP:

In the above code snippet we declared a parameter paramEmployeeTVP of type EmployeeTVP. Now we will load some data into the new parameter.

TVP as a life saver:

Let us a consider a scenario where every time a customer places an order , a customer row along with customer detail row is created and inserted into the database. Usually it is achieved by using different stored procedures. So if there are 20 orders for the customer then both the SP s called 20 times. Also we have to consider performing everything within a transaction. So there will be numerous round trips between application and database.

Now let us take a look how can TVP help us. We will create one stored procedures with two TVP. We will first assign all the values to the TVPs and then call the SP only once to perform the inserts. Also in this case we will not require a transaction as it will be within a SP. Below is the code snippet.

As we see we have inserted values to CustomerOrderDB and CustomerOrderDetailDB tables by passing values through TVPs. In the same way we can perform bulk inserts as well where the values already assigned to a TVP can be inserted to a table.

We can also perform bulk updates or deletes using a TVP. Below is the code:

A table is created and couple of records has been inserted in the above code. In the below code we have declared a user defined table type CustomerOrderTVP and @CustomerOrder is a TVP. We have loaded one value i.e. ‘Suv’ to the TVP.

Then we can perform the update to CustomerOrderDB based on the values of @CustomerOrder TVP.

TVP limitations:

• TVPs are read-only and cannot be used to return data. 
• Readonly keyword should be used while using the parameters in the stord procedures otherwise it will not compile. 
• The column values in a TVP cannot be updated and no data can be inserted or deleted from the TVP. 
• Alter Type….. is not supported as TVP schema cannot be altered. 
• Indexing is limited to primary index and unique constraints. 
• SQL server does not maintain statistics for TVPs.