Table-Valued Parameters (Database Engine)
Table-valued parameters are a new parameter type in SQL Server 2008. Table-valued parameters are declared by using user-defined table types. You can use table-valued parameters to send multiple rows of data to a Transact-SQL statement or a routine, such as a stored procedure or function, without creating a temporary table or many parameters.
Table-valued parameters are like parameter arrays in OLE DB and ODBC, but offer more flexibility and closer integration with Transact-SQL. Table-valued parameters also have the benefit of being able to participate in set-based operations.
Note
Transact-SQL passes table-valued parameters to routines by reference to avoid making a copy of the input data.
You can create and execute Transact-SQL routines with table-valued parameters, and call them from Transact-SQL code, managed and native clients in any managed language.
Creating and Using Table-Valued Parameters in Transact-SQL
Table-valued parameters have two primary components: a SQL Server type and a parameter that references that type. To create and use table-valued parameters, follow these steps:
Create a table type and define the table structure.
For information about how to create a SQL Server type, see User-Defined Table Types. For more information about how to define a table structure, see CREATE TABLE (Transact-SQL).
Declare a routine that has a parameter of the table type. For more information about SQL Server routines, see CREATE PROCEDURE (Transact-SQL) and CREATE FUNCTION (Transact-SQL).
Declare a variable of the table type, and reference the table type. For information about how to declare variables, see DECLARE @local\_variable (Transact-SQL).
Fill the table variable by using an INSERT statement. For more information about how to insert data, see Adding Rows by Using INSERT and SELECT.
After the table variable is created and filled, you can pass the variable to a routine.
After the routine is out of scope, the table-valued parameter is no longer available. The type definition remains until it is dropped.
To use a table-valued parameter in the SQL Server Native Client, see Table-Valued Parameters (SQL Server Native Client).
To use a table-valued parameter in ADO.NET, see the ADO.NET documentation.
Benefits
Table-valued parameters offer more flexibility and in some cases better performance than temporary tables or other ways to pass a list of parameters. Table-valued parameters offer the following benefits:
Do not acquire locks for the initial population of data from a client.
Provide a simple programming model.
Enable you to include complex business logic in a single routine.
Reduce round trips to the server.
Can have a table structure of different cardinality.
Are strongly typed.
Enable the client to specify sort order and unique keys.
Restrictions
Table-valued parameters have the following restrictions:
SQL Server does not maintain statistics on columns of table-valued parameters.
Table-valued parameters must be passed as input READONLY parameters to Transact-SQL routines. You cannot perform DML operations such as UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine.
You cannot use a table-valued parameter as target of a SELECT INTO or INSERT EXEC statement. A table-valued parameter can be in the FROM clause of SELECT INTO or in the INSERT EXEC string or stored-procedure.
Scope
A table-valued parameter is scoped to the stored procedure, function or dynamic Transact-SQL text, exactly like other parameters. Similarly, a variable of table type has scope like any other local variable that is created by using a DECLARE statement. You can declare table-valued variables within dynamic Transact-SQL statements and pass these variables as table-valued parameters to stored procedures and functions.
Security
Permissions for table-valued parameters follow the object security model for SQL Server, by using the Transact-SQL keywords: CREATE, GRANT, DENY, ALTER, CONTROL, TAKE OWNERSHIP, REFERENCES, EXECUTE, VIEW DEFINITION, and REVOKE.
Catalog Views
To obtain information that is associated with table-valued parameters, you can query the following catalog views: sys.parameters (Transact-SQL), sys.types (Transact-SQL), and sys.table_types (Transact-SQL).
Table-Valued Parameters vs. BULK INSERT Operations
Using table-valued parameters is comparable to other ways of using set-based variables; however, using table-valued parameters frequently can be faster for large data sets. Compared to bulk operations that have a greater startup cost than table-valued parameters, table-valued parameters perform well for inserting less than 1000 rows.
Table-valued parameters that are reused benefit from temporary table caching. This table caching enables better scalability than equivalent BULK INSERT operations. By using small row-insert operations a small performance benefit might be gained by using parameter lists or batched statements instead of BULK INSERT operations or table-valued parameters. However, these methods are less convenient to program, and performance decreases quickly as rows increase.
Table-valued parameters perform equally well or better than an equivalent parameter array implementation.
The following table shows which technology to use based on the speed of insert operations.
Data source |
Server logic |
Number of rows |
Best technology |
|---|---|---|---|
Formatted data file on the server |
Direct insert |
< 1000 |
BULK INSERT |
Formatted data file on the server |
Direct insert |
> 1000 |
BULK INSERT |
Formatted data file on the server |
Complex |
< 1000 |
Table-valued parameters |
Formatted data file on the server |
Complex |
> 1000 |
BULK INSERT |
Remote client process |
Direct insert |
< 1000 |
Table-valued parameters |
Remote client process |
Direct insert |
> 1000 |
BULK INSERT |
Remote client process |
Complex |
< 1000 |
Table-valued parameters |
Remote client process |
Complex |
> 1000 |
Table-valued parameters |
Examples
The following example uses Transact-SQL and shows you how to create a table-valued parameter type, declare a variable to reference it, fill the parameter list, and then pass the values to a stored procedure.
USE AdventureWorks;
GO
/* Create a table type. */
CREATE TYPE LocationTableType AS TABLE
( LocationName VARCHAR(50)
, CostRate INT );
GO
/* Create a procedure to receive data for the table-valued parameter. */
CREATE PROCEDURE usp_InsertProductionLocation
@TVP LocationTableType READONLY
AS
SET NOCOUNT ON
INSERT INTO [AdventureWorks].[Production].[Location]
([Name]
,[CostRate]
,[Availability]
,[ModifiedDate])
SELECT *, 0, GETDATE()
FROM @TVP;
GO
/* Declare a variable that references the type. */
DECLARE @LocationTVP
AS LocationTableType;
/* Add data to the table variable. */
INSERT INTO @LocationTVP (LocationName, CostRate)
SELECT [Name], 0.00
FROM
[AdventureWorks].[Person].[StateProvince];
/* Pass the table variable data to a stored procedure. */
EXEC usp_InsertProductionLocation @LocationTVP;
GO