• Login

DataSoft Corporation

Table-Valued Parameters (Database Engine)

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.

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.

 

Sage 50 and Peachtree Accounting does not support Stored Procedures.

Benefits

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 a 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.

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 SQL Server 2012 (11.x), table-valued parameters are also cached for parameterized queries.

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 the 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 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 the program, and performance decreases quickly as rows increase.

Table-valued parameters perform equally well or better than an equivalent parameter array implementation.

Related Links

Related Articles
Friday, 28 December 2018 Posted in Parameters
 DataSoft Store  Knowledgebase  Contact Us
 Sitemap  Privacy Policy
    Customer Service


Copyright © 2019 DataSoft Corporation All rights reserved.