Skip to content

Latest commit

 

History

History
113 lines (88 loc) · 6.71 KB

use-table-valued-parameters-database-engine.md

File metadata and controls

113 lines (88 loc) · 6.71 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic helpviewer_keywords monikerRange
Use table-valued parameters (Database Engine)
Learn how to use table-valued parameters.
WilliamDAssafMSFT
wiassaf
07/19/2024
sql
table-view-index
how-to
table-valued parameters
table-valued parameters, about table-valued parameters
parameters [SQL Server], table-valued
TVP See table-valued parameters
=azuresqldb-current||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current||=fabric

Use table-valued parameters (Database Engine)

[!INCLUDE SQL Server Azure SQL Database Azure SQL Managed Instance FabricSQLDB]

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 [!INCLUDE tsql] 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 [!INCLUDE tsql]. Table-valued parameters also have the benefit of being able to participate in set-based operations.

[!INCLUDE tsql] passes table-valued parameters to routines by reference to avoid making a copy of the input data. You can create and execute [!INCLUDE tsql] routines with table-valued parameters, and call them from [!INCLUDE tsql] code, managed and native clients in any managed language.

Benefits

A table-valued parameter is scoped to the stored procedure, function, or dynamic [!INCLUDE tsql] 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 [!INCLUDE tsql] statements and pass these variables as table-valued parameters to stored procedures and functions.

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.
  • Are cached like a temp table when used in a stored procedure. Starting with [!INCLUDE ssSQL11] and later versions, table-valued parameters are also cached for parameterized queries.

Permissions

To create an instance of a user-defined table type, or call a stored procedure with a table-valued parameter, the user must have EXECUTE and REFERENCES permissions on the type, or on the schema or database containing the type.

Limitations

Table-valued parameters have the following restrictions:

  • [!INCLUDE ssNoVersion] does not maintain statistics on columns of table-valued parameters.
  • Table-valued parameters must be passed as input READONLY parameters to [!INCLUDE tsql] 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.

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 1,000 rows.

Table-valued parameters that are reused benefit from temporary table caching. This table caching enables better scalability than equivalent bulk insert operations. Small row-insert operations might provide a small performance benefit 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.

Examples

The following example uses [!INCLUDE tsql] 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 in the sample AdventureWorks database.

/* 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 dbo. usp_InsertProductionLocation
   @TVP LocationTableType READONLY
      AS
      SET NOCOUNT ON
      INSERT INTO AdventureWorks2022.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 AdventureWorks2022.Person.StateProvince;
  
/* Pass the table variable data to a stored procedure. */
EXEC usp_InsertProductionLocation @LocationTVP;

The expected resultset is:

(181 rows affected)

Related content