Table-Valued Parameters in SQL Server

Table-valued parameters are a new feature introduced in SQL Server 2008. They provide a built-in way to send multiple rows of data from a client application to SQL Server in a single parameterized SQL statement.

The foundation of this functionality rests on the new User-Defined Table Types in SQL Server 2008. This allows users to register table definitions as well-known types. These table types can be used as local variables in batches or in the body of stored procedures, much like a strongly-typed version of the generic table variable available in previous versions of SQL Server. However, unlike the generic table variables, user defined table types can also be used as parameters for stored procedures or parameterized TSQL.

User-defined table types have numerous restrictions:

• A user-defined table type cannot be used as a column in a table or a field in a structured user-defined type.
• A nonclustered index cannot be created on a user-defined table type unless the index is the result of creating a PRIMARY KEY or UNIQUE constraint on the user-defined table type.
• A DEFAULT value cannot be specified in the definition of a user-defined table type.
• The user-defined table type definition cannot be modified after it is created.
• User-defined functions cannot be called within the definition of computed columns of a user-defined table type.
• A user-defined table type cannot be used as a table-valued parameter for user-defined functions.

User-defined table types have a further limitation when used as table-valued parameters; they are read-only within the parameterized statement or procedure:

You cannot update the column values in the rows of a table-valued parameter and you cannot insert or delete rows. To modify the data that is passed to a stored procedure or parameterized statement in table-valued parameter, you must insert the data into a temporary table or into a table variable.

User-defined table types can be used within ADO.NET by utilizing the standard SqlParameter type:

• The TypeName of the parameter must be set to the name of the user-defined data type, e.g., "dbo.PersonInfo"
• The SqlDbType must be set to SqldbType.Structured
• The Value for the parameter must represent data that matches the user-defined data type. System.Data.SqlClient supports table-valued parameters from a System.Data.DataTable or IList. Additionally, rows of data can be streamed to a table-valued parameter using System.Data.Common.DbDataReader or any derived classes, e.g., OracleDataReader.

Before table-valued parameters, developers had several options for mimicking this ability:

• Use a series of individual parameters to represent the values in multiple columns and rows of data. The amount of data that can be passed by using this method is limited by the number of parameters allowed. SQL Server procedures can have, at most, 2100 parameters. Server-side logic is required to assemble these individual values into a table variable or a temporary table for processing.
• Bundle multiple data values into delimited strings or XML documents and then pass those text values to a procedure or statement. This requires the procedure or statement to include the logic necessary for validating the data structures and unbundling the values.
• Create a series of individual SQL statements for data modifications that affect multiple rows, such as those created by calling the Update method of a SqlDataAdapter. Changes can be submitted to the server individually or batched into groups. However, even when submitted in batches that contain multiple statements, each statement is executed separately on the server.
• Use the bcp utility program or the SqlBulkCopy object to load many rows of data into a table. Although this technique is very efficient, it does not support server-side processing unless the data is loaded into a temporary table or table variable.