By: Diana Moldovan | Updated: 2011-03-30 | Comments (5) | Related: > Table Valued Parameters
Problem
Before SQL Server 2008, passing multiple rows of data to a stored procedure was not a straightforward process. This involved additional steps such as creating an XML document from the data values and passing it to the procedure which inserts / updates the target data or bulk inserting to a temp table. Fortunately SQL 2008 offers the table valued parameters (TVPs), which makes our lives a little bit easier. In this tip I'll show you how to use a SqlDataReader to stream rows of data to a table-valued parameter from within a .NET client application. Support for TVPs exists beginning with Visual Studio 2008 and ADO.NET 3.5.
Solution
Task Description
Suppose you have to periodically update the list price and the sale price for some of the records in a product table using data from a staging table, which may be located on another SQL instance / server. (Download the code for this tip)
TSQL code
I used a database called "Test", with the recovery model set to "simple".
First of all, let's create the table type on which the table valued parameter relies.
CREATE TYPE [dbo].[MatrixData] AS TABLE(
[ProductID] [int] NOT NULL,
[Price] [money] NOT NULL,
[SalePrice] [money] NOT NULL,
PRIMARY KEY CLUSTERED ([ProductID] ASC));
)
Then create the tables we will use. As you can see both the source and the target are indexed on the join column (ProductID). The ProductStaging table contains the source data (in our case ~1000 records) we'll use this to update the target ProductPrice table.
CREATE TABLE [dbo].[ProductStaging]( [SKU] [varchar](50) NOT NULL, [ProductID] [int] NOT NULL, [Price] [money] NOT NULL, [SalePrice] [money] NOT NULL, CONSTRAINT [PK_ProductStaging_1] PRIMARY KEY CLUSTERED([ProductID] ASC) ) ON [PRIMARY];
In this scenario, we need to update the prices and sale prices stored in the ProductPrice table, which has the following structure. The ProductPrice table contains ~40.000 records.
CREATE TABLE [dbo].[ProductPrice]( [ProductID] [int] NOT NULL, [Price] [money] NOT NULL, [SalePrice] [money] NULL, [Cost] [money] NULL, [Inventory] [int] NOT NULL DEFAULT 0, --Other columns here [UpdatedOn] [smalldatetime] NOT NULL DEFAULT GETDATE(), CONSTRAINT [PK_ProductPrice] PRIMARY KEY CLUSTERED([ProductID] ASC) ) ON [PRIMARY];
Here is the stored procedure which will take a parameter based on the MatrixData type. I'm also using another great feature SQL 2008 offers - the MERGE statement. In this example I'll only update the records that match between the source (ProductStaging table) and the target (ProductPrice table).
CREATE PROCEDURE [dbo].[DM_TVPUpdate]
@srcData MatrixData READONLY
AS
SET NOCOUNT ON;
MERGE dbo.ProductPrice
USING @srcData AS Source
ON ProductPrice.ProductID = Source.ProductID
WHEN MATCHED
THEN UPDATE SET
ProductPrice.Price = Source.Price,
ProductPrice.SalePrice = Source.SalePrice,
ProductPrice.UpdatedOn = GETDATE();
.NET C# code
For simplicity I've used a basic console application which calls the price update routine. The .NET code below opens a SqlDataReader ("srcData") which selects from the ProductStaging table and passes this reader as a table valued parameter to the DM_TVPUpdate stored procedure.
Please note that the type of the "src" parameter is SqlDbType.Structured. This is, according to MSDN, "a special data type for specifying structured data contained in table-valued parameters."
public static void PriceUpdate(string retrieveSource, string connStr)
{
using (SqlConnection conn = new SqlConnection(connStr),
conn_r = new SqlConnection(connStr))
{
conn.Open(); conn_r.Open();
using (SqlCommand cmd = new SqlCommand("DM_TVPUpdate", conn),
cmd_r = new SqlCommand(retrieveSource, conn_r))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd_r.CommandType = CommandType.Text;
using (SqlDataReader srcData = cmd_r.ExecuteReader())
{
SqlParameter src = cmd.Parameters.AddWithValue("@srcData", srcData);
src.SqlDbType = SqlDbType.Structured;
cmd.ExecuteNonQuery();
}
}
}
}
You can use not only a SqlDatareader, but any object derived from DbDataReader - such as System.Data.OracleClient.OracleDataReader or System.Data.EntityClient.EntityDataReader - to stream rows of data to a table-valued parameter. System.Data.SqlClient also supports populating table-valued parameters from DataTable and System.Collections.Generic.IEnumerable
The data you pass from the reader must match the TVP definition, i.e. the number of columns must be the same and the data types must be the same or be implicitly convertible.
In the main class of the console application you'll call the PriceUpdate routine like this:
using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
static void Main(string[] args){
string connStr = ConfigurationManager.AppSettings["DbConn"];
string sourceDataTSQL = ConfigurationManager.AppSettings["sourceDataTSQL"];
try{
//...
MyClass.PriceUpdate(sourceDataTSQL, connStr);
//...
}
catch{
//error trapping code here...
}
finally{
// your code here...
}
}
The parameter values are taken from the application's configuration file:
<CONFIGURATION>
<APPSETTINGS>
<ADD value="data source=MyServer;initial catalog=Test;
Integrated Security = SSPI" key="DBConn" />
<ADD value="SELECT ProductID, Price,
SalePrice FROM ProductStaging" key="sourceDataTSQL" />
<!--other values here-->
</APPSETTINGS>
</CONFIGURATION>
In this example both the staging table and the ProductPrice table are located in the same database; therefore I'm using only one connection string.
Under the Covers
To see what's happening behind the scenes, I've set up a server side trace which captures the TSQL SQL:BatchStarting event. As you can see in the picture below, the data from the reader is inserted into a table variable (@p2) having the MatrixData type. Afterwards @p2 is passed to the DM_TVPUpdate stored procedure as a parameter.
There are in our case ~1000 inserts to be done. If you try to use the profiler GUI and there are enough rows to insert, you may find out only that the trace "skipped rows".
To see the execution plan of the stored procedure which updates the price values, run this:
SELECT plan_handle, query_plan, objtype, dbid, objectid FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) WHERE objtype = 'Proc' AND OBJECT_NAME(objectid, dbid) = 'DM_TVPUpdate';
Here is the execution plan "broken" in 2 pictures. The upper image represents the rightmost part of the plan:
The sys.dm_exec_query_stats DMV offers data regarding the logical reads and CPU time.
SELECT SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.TEXT) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1), qs.execution_count, qs.total_logical_reads, qs.last_logical_reads, qs.total_logical_writes, qs.last_logical_writes, qs.total_physical_reads, qs.last_logical_reads, qs.total_worker_time, qs.total_elapsed_time/1000000 total_elapsed_time_in_S FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt ORDER BY qs.total_logical_reads DESC;
If you're using a table type for which the join column (ProductID) is not indexed, you'll obtain, for example, a bigger number of logical reads (on my machine: 4366 vs. 15) and a bigger worker time (on my machine: 24414 vs. 16601)
Please bear in mind that I worked in a test environment, with little additional workload, if any.
According to the documentation, TVPs are materialized as temporary tables in tempdb; therefore performance with large datasets should be good. When you pass a TVP to a stored procedure you pass in fact a reference to that temporary table. However you should consider that SQL Server does not maintain statistics on table-valued parameters, so thoroughly test this approach in your environment. Also you should monitor tempdb usage.
You can see the structure of the temp table by querying the sys.tables and sys.columns catalog views:
USE tempdb; SELECT * FROM sys.tables; SELECT * FROM sys.columns WHERE object_id IN (SELECT object_id FROM sys.all_objects WHERE type_desc = 'USER_TABLE');
Next Steps
- This is an excellent introduction to the table valued parameters: http://www.mssqltips.com/sqlservertip/1483/using-table-valued-parameters-tvp-in-sql-server-2008/
- MSDN offers details about TVP usage from a .NET client application: http://msdn.microsoft.com/en-us/library/bb675163.aspx#Y1596
- Be sure to review here the MERGE statement best practices. Indexing the join columns is just one of them.
- One of the best resources I can recommend is SQL Server MVP Deep Dives. Chapter 16, written by Don Kiely, offers the essential information in a nutshell.
You can download all of the code for this tip.
- After downloading the zip file, restore the included database, open the solution in VS 2010 and change the connection string and log file location values in the App.config file according to your environment.
- Program.cs is the "main" file. Trace.cs contains code regarding the error log file. Worker.cs contains the PriceUpdate method.
- I used VS 2010; therefore you won't be able to open the solution with VS 2008. If you need to work with VS 2008, create a new "console application" project, copy the files I've sent in the new directory (Program.cs will be replaced) and open the solution. In the "solution explorer" pane right click the project name, choose Add -> Existing Item and add Worker.cs, Trace.cs and App.Config. Make sure that the namespace is OK (i.e. is the same as project name) in each .cs file.
About the author
Diana Moldovan is a DBA and data centric applications developer with 6 years of experience covering SQL 2000, SQL 2005 and SQL 2008.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2011-03-30
