SQL Server 2008 Table variable parameter (table-valued parameter) usage

The table-valued parameter is a new parameter type in SQL Server 2008. Table-valued parameters are declared using a user-defined table type. With table-valued parameters, you can send multiple rows of data to a Transact-SQL statement or routine, such as a stored procedure or function, without having to create a temporary table or many parameters.

Table-valued parameters are similar to parameter arrays in OLE DB and ODBC, but have greater flexibility and are more tightly integrated with Transact-SQL. Another advantage of table-valued parameters is the ability to participate in data-set-based operations.

(NOTE: Transact-SQL passes a table-valued parameter by referencing a usually to avoid creating a copy of the input data.) )

Creating and using table-valued parameters in Transact-SQL

A table-valued parameter has two main parts: the SQL Server type and the parameters that reference that type. To create and use a table-valued parameter, follow these steps:

1. 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 the table structure, see CREATE table (Transact-SQL).
2. Declares a routine that has a table-type parameter. For more information about SQL Server routines, see Create PROCEDURE (Transact-SQL) and create FUNCTION (Transact-SQL).
3. Declares a table-type variable and references the table type. For information about how to declare variables, see DECLARE @local_variable (Transact-SQL).
4. Use the INSERT statement to populate the table variable. For more information about how to insert data, see Add rows by using Insert and SELECT.
5. After you create and populate a table variable, you can pass the variable to the routine.
   
   After a routine goes out of scope, the table-valued parameter is no longer available. The type definition is persisted until it is deleted.

To use table-valued parameters in SQL Server Native client, see table-valued Parameters (SQL Server Native client).

To use table-valued parameters in ADO, see your ADO.

Advantages

Table-valued parameters offer greater flexibility, and in some cases, better performance than temporary tables or other methods that pass parameter lists. Table-valued parameters have the following advantages:

• Locks are not acquired when data is first populated from the client.
• Provides a simple programming model.
• Allows complex business logic to be included in a single routine.
• Reduce the round-trip to the server.
• A table structure that can have different cardinality.
• is strongly typed.
• Enables the client to specify a sort order and a unique key.

Limit

Table-valued parameters have the following limitations:

• SQL Server does not maintain statistical information for table-valued parameter columns.
• The table-valued parameter must be passed as an input READONLY parameter to the Transact-SQL routine. DML operations such as UPDATE, DELETE, or INSERT cannot be performed 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. Table-valued parameters can be in the FROM clause of the SELECT into, or in the INSERT EXEC string or stored procedure.

Scope

As with other parameters, the scope of a table-valued parameter is a stored procedure, function, or dynamic Transact-SQL text. Similarly, a table-type variable has scope just like any other local variable created using the DECLARE statement. Table-valued variables can be declared within dynamic Transact-SQL statements, and these variables can be passed as table-valued parameters to stored procedures and functions.

Security

The Permissions for table-valued parameters use the following Transact-SQL keywords to follow the object-Safe mode of the SQL Server: CREATE, GRANT, DENY, ALTER, CONTROL, take OWNERSHIP, REFERENCES, EXECUTE , VIEW DEFINITION, and REVOKE.

catalog view

To get the information associated with a table-valued parameter, you can query the following catalog views: sys.parameters (Transact-SQL), sys.types (Transact-SQL), and sys.table_types (Transact-SQL).

table-Valued parameters and BULK INSERT operations

Table-valued parameters are used in a similar way to other data-set-based variables, but frequent use of table-valued parameters will be faster than large data sets. The start-up cost of a bulk operation is larger than the table-valued parameter, and the table-valued parameter performs well when inserting fewer than 1000 rows.

Reused table-valued parameters can benefit from the temporary table cache. This table caching feature provides better scalability than a peer-to-BULK INSERT operation. When you use a small row insert operation, you can obtain small performance improvements by using a parameter list or a bulk statement instead of a BULK insert operation or table-valued parameter. However, these methods are not very convenient to program, and as the rows increase, the performance decreases rapidly.

Table-valued parameters are fairly or even better than the equivalent parameter pattern implementations on the execution performance.

The following table describes which technology should be used depending on the speed of the insert operation.

Data Source	Server Logic	Number of rows	Best Technology
Formatted data files on the server	Insert directly	< 1000	BULK INSERT
Formatted data files on the server	Insert directly	> 1000	BULK INSERT
Formatted data files on the server	Complex	< 1000	Table-Valued parameters
Formatted data files on the server	Complex	> 1000	BULK INSERT
Remote client process	Insert directly	< 1000	Table-Valued parameters
Remote client process	Insert directly	> 1000	BULK INSERT
Remote client process	Complex	< 1000	Table-Valued parameters
Remote client process	Complex	> 1000	Table-Valued parameters

Example

The following example uses Transact-SQL and demonstrates how to create a table-valued parameter type, declare a variable to reference it, populate the parameter list, and then pass the value to the stored procedure.

USE AdventureWorks; GO / * Create table value parameter type * / CREATE TYPE LocationTableType AS TABLE (LocationName VARCHAR (50) , CostRate INT); GO / * Create a process to obtain the parameter data of the table value * / 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 to reference 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 data of the table variable to the stored procedure * / EXEC usp_InsertProductionLocation @LocationTVP; GO

SQL Server 2008 Table variable parameter (table-valued parameter) usage