More on Table-valued Parameters (TVP’s)


While finalising some slides for my presentation at the Canberra .Net User Group on Using XML and TVP’s to Transport Relational Data, I stumbled across a couple of posts by Bob Beauchemin relating to how TVP’s cause plan recompiles when called from ADO.NET.  Given that I published a post back in May 2011 about using TVP’s with ADO.NET, I thought I’d provide a brief overview of what Bob is referring to (you can find Bob’s original content links below).

Overview

Let’s assume we have a Table Type called udtt_Passenger which contains 1 column of type BIGINT and a stored procedure called usp_AddPassenger which has one parameter of type udtt_Passenger.  Let’s also assume that we have a SQL Server Table Variable and an ADO.NET Datatable each containing 5 rows of data.  If we were to call our stored procedure from SSMS (passing in our SQL Server Table Variable) and ADO.NET (passing in our ADO.NET Datatable), SQL Profiler would report the following:

declare @t [Person].[udtt_Passenger]
insert @t values(1)
insert @t values(2)
insert @t values(3)
insert @t values(4)
insert @t values(5)
exec usp_AddPassenger @t

Although both calls result in the exact same code being reported in SQL Profiler, it’s the mechanism used to do the call that’s different.

When SSMS executes this code, it does so via a SQLStmt call so the code is executed as a batch i.e. INSERTS + EXEC therefore a query plan for the batch and stored procedure is cached and reused .  When ADO.NET executes this code, it does so via a RPC call therefore only the query plan for the stored procedure is cached, not the INSERTS.  It is this difference that causes plan compiles to occur for each INSERT statement when using ADO.NET.

Anyway, to cut a long story short, this is by design and according to Microsoft, has minimal impact on performance (SQL Connect Item submitted by Bob).

References

Part 4 – Table-Valued Parameters (TVP’s): Table what?


Introduction

This is the fourth post in a series of five on Using TVP’s and XML to Transport Relational Data.

In this post we’ll take a look at how Table-Valued Parameters (TVP’s) can be used to send multiple rows of data to a Transact-SQL statement or a routine, such as a stored procedure.  We’ll also look at how .Net Developers can use TVP’s to reduce the number of SQL Server calls required to handle data modifications.

For details of other posts in this series, please refer to Using TVP’s and XML to Transport Relational Data.

Brief History

Table-valued parameters are declared by using user-defined table types and were introduced in SQL Server 2008.

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.

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

Creating Tables with .Net

In Part 2 – OpenXML: What is it and how do I use it? I created an ADO.NET Dataset (dsPassenger) consisting of two tables; Passenger and PAX.  I will use these same tables to pass data to a stored procedure that accepts Table-valued Parameters.

Creating and Using Table-Valued Parameters

The first step is to create the required table types and define their table structure (For full details on how to create a SQL Server type, see User-Defined Table Types).
 
image
 
CREATE TYPE [Person].[udtt_Passenger] AS TABLE
(
	[Newsletter] [bit] NOT NULL,
	[PersonID] [uniqueidentifier] NOT NULL,
	[FirstName] [varchar](50) NOT NULL,
	[LastName] [varchar](50) NOT NULL,
	PRIMARY KEY CLUSTERED 
	(
		[PersonID] ASC
	)
)
GO

CREATE TYPE [Person].[udtt_PAX] AS TABLE
(
	[PaxID] [uniqueidentifier] NOT NULL,
	[FirstName] [varchar](50) NOT NULL,
	[LastName] [varchar](50) NOT NULL,
	[PaxOrder] [tinyint] NOT NULL,
	PRIMARY KEY CLUSTERED 
	(
		[PaxID] ASC	)
)
GO
Hint: An easier way to do this is to generate a Create Table Script based on an existing table and then modify this script to use the CREATE TYPE syntax.
 
The next step is to create a stored procedure that accepts the Passenger and PAX UDTT’s as parameters
 
CREATE PROCEDURE [Person].[usp_UpdatePassengerDetailTVP]
(
	@Passenger [Person].[udtt_Passenger] READONLY,
	@Pax [Person].[udtt_Pax] READONLY
)
AS 

SET NOCOUNT ON

UPDATE	[Person].[Detail]
SET		FirstName = P.FirstName,LastName = P.LastName,
		Newsletter = P.Newsletter
FROM	@Passenger P
WHERE	[Person].[Detail].PersonID = P.PersonID

UPDATE	[Person].[Pax]
SET		FirstName = P.FirstName,LastName = P.LastName,
		PaxOrder = P.PaxOrder
FROM	@Pax P
WHERE	[Person].[Pax].PAXID = P.PAXID

IF @@ERROR <> 0
    -- Return 99 to the calling program to indicate failure.
    RETURN 99
ELSE
    -- Return 0 to the calling program to indicate success.
    RETURN 0
    
SET NOCOUNT ON
Now that I’ve created the stored procedure, I can test it with some simple code.
 
-- Get current record details
SELECT	PersonID,FirstName,LastName,Newsletter
FROM	[Person].[Detail]
WHERE	PersonID = '7a7c18a0-ad47-e011-9c71-00155d991421'
  
SELECT	PaxID,FirstName,LastName,PaxOrder
FROM	[Person].[PAX]
WHERE	PaxID IN ('868b8fde-068a-4468-b9b6-9660166e630d',
		'bb2770e0-b0e0-479f-8c03-1330ce87cb64')
		
-- Create local table type variables and populate
-- with data
DECLARE @Passenger [Person].[udtt_Passenger]
DECLARE @Pax [Person].[udtt_Pax]

INSERT INTO @Passenger (PersonID,FirstName,LastName,Newsletter)
VALUES ('7a7c18a0-ad47-e011-9c71-00155d991421','Jeff','Wharton',0)

INSERT INTO @Pax (PaxID,FirstName,LastName,PaxOrder)
VALUES ('868b8fde-068a-4468-b9b6-9660166e630d','Lily','Wharton',3),
('bb2770e0-b0e0-479f-8c03-1330ce87cb64','Ben','Wharton',2)

-- Execute the stored procedure
EXEC [Person].[usp_UpdatePassengerDetailTVP] @Passenger, @PAX

-- Get updated record details
SELECT	PersonID,FirstName,LastName,Newsletter
FROM	[Person].[Detail]
WHERE	PersonID = '7a7c18a0-ad47-e011-9c71-00155d991421'
  
SELECT	PaxID,FirstName,LastName,PaxOrder
FROM	[Person].[PAX]
WHERE	PaxID IN ('868b8fde-068a-4468-b9b6-9660166e630d',
		'bb2770e0-b0e0-479f-8c03-1330ce87cb64')
image
 
Now that I know the stored procedure functions correctly,  let’s take a look at the .Net code required to pass data to the stored procedure.

Using .NET to Pass Table-Valued Parameters

The .Net code I need to use to call the stored procedure is practically the same as discussed in Part 2 – OpenXML: What is it and how do I use it?.  All I need to do is is change the stored procedure name to [Person].[usp_UpdatePassengerDetailTVP]  and replace the existing XML parameter with two new parameters of type SqlDbType.Structured:

db.AddInParameter(dbCommand, "@Passenger", SqlDbType.Structured, 
    dsPassengers.PassengerDataTable);
db.AddInParameter(dbCommand, "@Pax", SqlDbType.Structured, 
    dsPassengers.PaxDataTable);

Conclusion

As you can see from the above example, using Table-valued Parameters to send multiple rows of data to SQL Server is very simple to implement.  That said, there are a couple of issues that make using TVP’s a bit of a pain (if not a waste of time):

  • Once you’ve created a user-defined table types you can’t modify it.  You need to drop and recreate.
  • You can’t drop a user-defined table types if it’s being referenced by another object i.e. in this example, I cannot drop my UDDT’s as they are being referenced by stored procedure [Person].[usp_UpdatePassengerDetailTVP], thus I can not modify the UDDT’s unless I remove all references
  • The order of columns in a Datatable need to be in the exact same order as the UDDT as no column mapping is applied.

Part 1 – Using TVP’s and XML to Transport Relational Data


Introduction

I’ve been using XML as a means of sending multiple rows of data to SQL Server ever since OpenXML was released in SQL Server 2000.  Although 2000 didn’t have an XML data type, the OpenXML Rowset Provider enabled SQL Server to shred a XML string into a rowset which could then be used in Transact-SQL statements in which rowset providers such as a table, view, or the OPENROWSET function can appear.  Gone were the days of looping through rows of data and sending each row to SQL Server one at a time.  Just when I thought things couldn’t get any better, Microsoft released SQL Server 2005.

One of the new features of 2005 was the introduction of the native XML data type. This new type allowed users to create tables that had one or more columns of type XML in addition to relational columns, create XML variables and parameters, and use a subset of the XQuery Language to query XML data.  The XML data type could also be used with the OpenXML Rowset Provider.  Developers now had two easy methods of sending multiple rows of data to SQL Server.

With the release of SQL Server 2008 came Table-valued parameters (TVP).  Table-valued parameters are declared by using user-defined table types and can be used 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.  You guessed it; another method.

The Problem

Developers can now use a number of different methods for sending multiple rows of data to SQL Server and sometimes choosing the correct method to use can be frustrating.  Which one is faster?  Which one is easier to implement?  Can I use a mixture of both? Why isn’t there somewhere I can look for a comparison on all these technologies?  These are some of the questions I’ve been asking myself over the last couple of weeks.

What’s This Post All About?

I originally thought of documenting many of the things I discovered about these technologies in a word document to be stored away and potentially lost over the coming months.  I also thought about throwing it all away and sticking with my current method (OpenXML) as I found I had more question than answers and it was all starting to sound too difficult.  In the end I was convinced (by a colleague) to blog about my findings.

The purpose of this series of posts is to provide other developers, DBD’s and DBA’s with an insight into the above mentioned technologies and hopefully assist them with choosing and implementing a technology (or technologies) to use within their applications.  I’m also hoping that others will provide comment on their experiences with these technologies.

Scope

OpenXML:  What is it and how do I use it?
XQuery: Please don’t use OpenXML!
Table-Valued Parameters (TVP’s): Table what?
OpenXML and XQuery Optimisation Tips

Final Word

Estimated post dates:

OpenXML: 27th May 2011
XQuery: 28th May 2011
Table-Valued Parameters (TVP’s): 29th May 2011
OpenXML and XQuery Optimisation Tips: 12th June 2011

%d bloggers like this: