In this article we are going to discuss the new parameter type introduced in SQL SERVER 2008.
Untill SQL SERVER 2005, it was not possible to pass a table variable or a bunch of rows in a single parameter. It was instead achieved in one of two ways.
Delimited Strings
If we want to pass multiple values in a single parameter, we could construct a delimited string and it will be split up into multiple rows in the stored procedure, like that we will be doing. But it lacks a performance issue. When there is more than 50 values in that string, it will be costlier execution and results in slower execution of the query.
Bulk Insert
Another approach used to achieve this requirement is Bulk Insert functionality. But again the performance for that is poor.
To overcome all these difficulties, Microsoft has introduced a new parameter type named Table Valued Parameter.
Table Valued Parameter
Table Valued Parameter is the new parameter type which is available with SQL SERVER 2008. It's mainly used to send multiple rows to a T-SQL, such as stored procedures or functions, without creating any temporary table or splitting up the delimited strings or many parameters. It also has the benefit of being able to participate in the set based operations.
Merits of TVP
- It provides a simple programming model
- It enables you to include complex logic in a single routine
- It reduces the round trip to the server
- Performance wise it is better compared with temporary tables or a list of parameters.
- It can have a table structure
A Few Restrictions
- It does not maintain statistics on columns of Table Valued Parameter
- Parameters will be passed as read-only; we cannot do DML operations on it.
Code Without TVP
Code With TVP
Hope you enjoyed this session and got a clear idea of Table Valued Parameter.