Saturday, June 6, 2015

SQL - Table-Valued Parameters (TVPs)

Table-Valued Parameter is the most amazing feature in SQL Server which was introduced in SQL Server 2012. 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. We can declare table-valued variables within dynamic Transact-SQL statements and pass these variables as table-valued parameters to stored procedures and functions. 
This is extremely useful in and of itself, but arguably the most compelling feature of TVPs is their ability to marshal an entire set of rows across the network, from your .NET client to your SQL Server database, with a single stored procedure call (one roundtrip) and a single table-valued parameter.

A TVP is based on a user-defined table type, which is capable to describe the schema for a set of rows that can be passed to stored procedures and UDFs. It’s helpful to begin understanding TVPs by first comparing them to similar “set” constructs, such as table variables, temp tables, and Common Table Expressions (CTEs). User can treat a TVP, table variable, temporary table, or CTE just like an ordinary table or view in virtually any scenario.

As we know that CTEs and table variables store their row data in memory—assuming reasonably sized sets that don’t overflow the RAM cache allocated for them, in which case, they do push their data into tempdb. In contrast, a TVP’s data is always stored in tempdb. When user first populates a TVP, SQL Server creates a table in tempdb to back that TVP as it gets passed from one stored procedure (or UDF) to another. Once the stack unwinds and the TVP falls out of scope in your T-SQL code, SQL Server cleans up tempdb automatically. User never interacts directly with tempdb, because TVPs provide a total abstraction over it.

How to create Table Value Type Parameter in database
Table-valued parameters are declared by using user-defined table types. 
----- Create Type in database with body
Create Type Employeestvp as Table
(
EmpId Varchar(8),
EmployeeName Varchar(30),
DeptId Varchar(5),
EmployeeRatePerDay decimal(18,2)
)
TVP types are displayed in Visual Studio’s SQL Server Object Explorer in the User-Defined Table Types node beneath Programmability | Types as shown below:



User-defined table types that can be used for TVPs displayed in SQL Server Object Explorer

How to use TVPs to insert values-

The procedure doesn’t know or care how the caller populates the TVP before it is used as the source for the INSERT INTO…SELECT statement. For example, the caller could manually add one row at a time, as follows:   
---- declare variable TVPs type
DECLARE @Employeestvp AS Employeestvp

------ insert values in tvps variable  
INSERT INTO @Employeestvp VALUES
('E0010''Arjun Smith''D001', 191 )
INSERT INTO @Employeestvp VALUES
('E0011''Lucy White''D001', 185)
INSERT INTO @Employeestvp VALUES
('E0012''Lucky Gray''D001', 178)
INSERT INTO @Employeestvp VALUES
('E0013''Rose Smith''D002', 172)
INSERT INTO @Employeestvp VALUES
('E0014''Read Milk''D002', 166)

---- select values from the variables here 
SELECT * FROM @EmployeesTVP

Emp Id
Employee Name
Dept Id
EmployeeRatePerDay
E0010
Arjun Smith
D001
191
E0011
Lucy White
D001
185
E0012
Lucky Gray
D001
178
E0013
Rose Smith
D002
172
E0014
Read Milk
D002
166
Creating a stored procedure that accepts TVPs

Enter TVPs. Now we can create a single stored procedure having a TVPs as the parameter for the Employee Master as shown below:
---- Create Proc to insert values from table type
CREATE PROCEDURE tvp_sp_InsertEmployees
(
----- define table value type parameter
@EmployeesTVP as Employeestvp READONLY
)
AS
begin
---- Insert value into target table
---- from table value type parameter
INSERT INTO [dbo].[EmployeeMaster]
(EmpId,EmployeeName,DeptId, EmployeeRatePerDay)
---- Pull values from table value type parameter
SELECT EmpId,
EmployeeName,
DeptId,
EmployeeRatePerDay FROM @EmployeesTVP

End

How to call stored procedure to insert values from TVPs:

Now, we have created stored procedure and defined the input parameter as table value type which we have already created in our database also. We have a program (stored procedure, table value parameter and target table), so it will be very easy to insert multiple records into our data table as given below:
---- declare table value parameter variable
DECLARE @Employeestvp AS Employeestvp

------ insert values in tvps variable 
INSERT INTO @Employeestvp VALUES('E0010', 'Arjun Smith', 'D001', 191 )
INSERT INTO @Employeestvp VALUES('E0011', 'Lucy White', 'D001', 185)
INSERT INTO @Employeestvp VALUES('E0012', 'Lucky Gray', 'D001', 178)
INSERT INTO @Employeestvp VALUES('E0013', 'Rose Smith', 'D002', 172)
INSERT INTO @Employeestvp VALUES('E0014', 'Read Milk', 'D002', 166)

---- call the store procedure and pass tvp parameters     
EXEC tvp_sp_InsertEmployees @Employeestvp

---- Pull Value from our data table after insert records
SELECT EmpId,
EmployeeName,
DeptId,
EmployeeRatePerDay FROM [dbo].[EmployeeMaster]
Emp Id
Employee Name
Dept Id
EmployeeRatePerDay
E0010
Arjun Smith
D001
191
E0011
Lucy White
D001
185
E0012
Lucky Gray
D001
178
E0013
Rose Smith
D002
172
E0014
Read Milk
D002
166
Conclusion
Table-valued parameters provide an easy way to marshal multiple rows of data from a client application to SQL Server without requiring multiple round trips or special server-side logic for processing the data. Table-valued parameters are declared by using user-defined table types. 

1 comment:

  1. This feature was introduced in SQL Server 2008 :) anyways nice article. Keep posting.

    ReplyDelete

Popular Posts