TVPs are a feature of Microsoft SQL Server. Introduced in SQL Server 2008, TVPs allow for sending a structured table of data to a stored procedure or function. Using TVPs can improve performance over sending multiple rows as XML or doing row-by-row processing.

Table-Valued Parameters (TVPs) are a feature of Microsoft SQL Server. Introduced in SQL Server 2008, TVPs allow for sending a structured table of data to a stored procedure or function. The mechanism used to hold the data is a table variable, and the structure of that table variable -- a user-defined table type -- has to be predefined in the database prior to being able to declare it as a stored procedure input parameter type (i.e. the structure cannot be determined at run-time / ad hoc).

While it is possible to declare a table variable from a user-defined table type, either for convenience of using the same structure many time or to just pass data between stored procedures, a major benefit of TVPs is in giving application code (.NET only at the moment? -- still no support in JDBC as of version 6.0 "Preview" / November, 2015) the ability to pass in a structured array / collection of data instead of either serializing multiple rows into an XML document or making individual insert calls. TVPs allow for streaming the data from .NET code into SQL Server so that multiple rows can be handled in true set-based fashion. XML serialization also allows for treating multiple rows as a set, but TVPs have the advantages of:

  • being strongly typed fields (which helps identify bad data before it gets to the database),
  • allowing for constraints such as NOT NULL, CHECK, and UNIQUE (which helps identify bad data before it gets to the database),
  • being more efficient since XML requires:
    • serialization in the app layer,
    • deserialization using the .nodes() function in the database (or OPENXML)
    • more data validation (often, but not always)

For additional information, please see the MSDN documentation for Use Table-Valued Parameters.

history | excerpt history