In-Depth

Reduce Code and Server Roundtrips

Utilize table-valued parameters to write less code and to achieve higher application performance.

• By Paul Delcogliano
• 04/01/2008

Table-valued parameters are the long-awaited solution to a specific SQL Server shortcoming that has plagued developers for quite some time: SQL Server doesn't provide a built-in way to send multiple rows of data as a single parameter to a stored procedure or a parameterized query.

This seemingly minor limitation has led to major headaches for developers. Workarounds to this problem abound. Some tried-and-true solutions include creating delimited strings, using additional parameters, building the SQL statements dynamically, and/or making additional server roundtrips. More recently, developers have started to use XML to send multiple values as one stored procedure parameter.

All of these solutions work, but none are ideal. At the least, extra code is required to marshal and process the data, both on the client and on the server. Sometimes that extra code makes debugging difficult, as is the case with dynamically generated SQL statements. At worst, you're forced to make additional server roundtrips, reducing application performance.

Table-valued parameters (TVPs) change all of this. TVPs provide a consistent, built-in mechanism to send multiple rows of data as a single parameter to a stored procedure or parameterized command. And they accomplish this feat without requiring special logic or extra roundtrips to do so. With TVPs, you can pass multiple rows of data using a DataTable, DbDataReader, or Generic.IList<SqlDataRecord> just as you would pass an INT or NVARCHAR parameter.

Table-valued parameters are a new data type in SQL Server 2008. TVPs are based on another new SQL Server 2008 feature called User-Defined Table Types (UDTTs). A full discussion of UDTTs is beyond the scope of this article. For the purposes of understanding TVPs, all you need to know about UDTTs is that they're a user-defined type that are defined as a table. Fundamentally, table-valued parameters are merely parameters whose data type is a user-defined table. You can think of TVPs as arrays that you can pass as stored procedure parameters.

Table-valued parameters have several benefits. First, standard T-SQL SELECT statements can be used to access column values. Second, table-valued parameters are strongly typed. Third, the size of a table-valued parameter is only limited by the amount of server memory. Fourth, columns in the user-defined table can be indexed.

TVPs have their restrictions, too, but they are minor considering their intended use. In particular, TVPs are read-only. You cannot insert, update, or delete rows in a TVP from within a stored procedure body, and you must supply the READONLY keyword when declaring the parameter. TVPs are input-only. They cannot be declared as OUTPUT parameters. Also, you can't modify their structure using ALTER TABLE.

Life Before TVPs
Let's look at a situation where TVPs come in handy. Suppose you had to build an application whose requirements were to display orders in a ListBox control on a Windows form. The form allows users to search for orders by selecting one or more territories from the ListBox (see Figure 1).

Today, a typical solution for implementing these requirements is to build a delimited string comprised of all selected territory IDs and names on the client, and pass that string to the server as a stored procedure parameter (see Listing 1). Once you build a delimited string, you pass it to the up_GetOrdersByTerriory_Delimited stored procedure using a SqlParameter object defined as VARCHAR, named @territories:

da.SelectCommand.Parameters.Add (new 
   SqlParameter("@territories", 
   territoryList));

The client-side code is straightforward, but the same can't be said for the T-SQL code required to parse a delimited string parameter (see Listing 2). This solution works, but it suffers from numerous shortcomings that are common with delimited strings. The primary issue is that approximately 75 percent of the T-SQL in the procedure has no purpose other than parsing the territory list parameter and storing its values in the table variable. That's a lot of code to have to maintain and debug just to pass a parameter. The second issue is that this solution uses a table variable declared inside of the procedure to store the parsed string values. Since table variables are scoped inside of stored procedures, they can't be re-used by any other objects in the database. A third issue is related to passing all of the values as one big delimited string. Passing the parameter values this way forces all of the values to be strings; you've just lost your strongly typed values. ID arrives at the server as a VARCHAR type. In order to join the Orders table to the table variable, ID has to be cast to INT when it's inserted into the table variable. Additionally, if you passed characters such as "XYZ" instead of numeric values for ID, the application would compile successfully but fail at runtime. Finally, unexpected errors can occur if a delimiter character is part of one of the parsed values, or if the length of the string is larger than the parameter's defined length.

Eliminate SQL Shortcomings with TVPs
The potential of table-valued parameters is realized when you apply them to a scenario like the one discussed thus far. The TVP solution eliminates all of the shortcomings of the delimited string approach.

The first step when using a TVP is to create a UDTT. This UDTT serves as the foundation for the table-valued parameter. The syntax for creating the UDTT is very similar to that of creating an ordinary table, or that of defining a table variable:

CREATE TYPE TerritoryTableType AS TABLE
(
   ID int not null,
   TerritoryName nvarchar(50) not null 
)

UDTTs such as the TerritoryTableType promote strong data-typing. The values entered into the ID column are INT and the TerritoryName values are NVARCHAR. The necessity to convert data types when joining to the Orders table is eliminated. Additionally, UDTTs are visible to any object within the database, permissions permitting, making them re-usable throughout the database.

With the type defined, you can create parameters and variables that are defined as the type. The up_GetOrdersByTerri-tory_TVP stored procedure returns the same result set as up_GetOrdersByTerritory_Delimited; however, it uses a TVP to return its results:

CREATE PROCEDURE 
   up_GetOrdersByTerritory_TVP
(
   @territoryList TerritoryTableType 
      READONLY
)

The first thing you should notice about the @territoryList parameter is that it's declared as type TerritoryTableType, the UDTT created previously. The @territoryList parameter is now a strongly typed parameter that accepts multiple rows of data.

Another note about the @territoryList parameter is that it has the READONLY attribute applied. In the February CTP release of SQL Server 2008, table-valued parameters are not updatable. The read-only restriction might be removed in the release version of SQL Server 2008. However, this article is based on a CTP release, so the stored procedure won't compile if the READONLY attribute isn't included.

The complete definition of the up_GetOrdersByTerritory_TVP procedure is straightforward:

CREATE PROCEDURE 
   up_GetOrdersByTerritory_TVP
(
   @territoryList TerritoryTableType 
      READONLY
)

AS

BEGIN
SET NOCOUNT ON

   SELECT o.OrderDate, o.SalesOrderNumber, 
      o.TotalDue, o.SubTotal, o.TaxAmt,
      t.TerritoryName, o.Status, o.ShipDate, 
      o.AccountNumber
   FROM Sales.SalesOrderHeader o
      INNER JOIN @territoryList t 
         ON o.TerritoryID = t.ID
  
SET NOCOUNT OFF

END

Note the reduction in code when compared to up_GetOrdersByTerritory_Delimited. The code reduction is due entirely to the elimination of the logic that parses the delimited string. With the parsing logic removed, the procedure now joins the Orders table directly to the @territoryList parameter on the ID column. Executing a stored procedure with a TVP requires some changes to the ADO.NET code to pass values to the procedure using a table-valued parameter.

The method GetSelectedTerritoriesForTVP is remarkably similar to the client-side code used in the delimited string example:

private TerritoryDS.TerritoryDataTable 
   GetSelectedTerritoriesForTVP()
{
   TerritoryDS.TerritoryDataTable 
      functionResult = new 
      TerritoryDS.TerritoryDataTable();
   TerritoryDS.TerritoryRow territory;

   // build the data table containing selected 
   // territories to pass as a stored procedure parameter

   for (int i = 0; i < 
      this.lstTerritory.SelectedIndices.Count; i++)
   {
      territory = this._territoryDS.Territory[
         this.lstTerritory.SelectedIndices[i]];
      functionResult.AddTerritoryRow(
         territory.ID, territory.TerritoryName);
   }

   return functionResult;
}

The client still loops through the selected territories, but instead of building a delimited string, the code now inserts the selected values as DataRows into a new instance of a strongly typed DataTable whose schema matches that of the TerritoryTableType:

TerritoryDS.TerritoryDataTable functionResult 
   = new TerritoryDS.TerritoryDataTable();
TerritoryDS.TerritoryRow territory;

for (int i = 0; i < 
   this.lstTerritory.SelectedIndices.Count; i++)
{
   territory = this._territoryDS.Territory[
      this.lstTerritory.SelectedIndices[i]];
   functionResult.AddTerritoryRow(
      territory.ID, territory.TerritoryName);
}

Next, examine the details of the method call to the up_GetOrdersByTerritory_TVP stored procedure using common ADO.NET objects:

public static OrdersDS GetOrdersWithTVP(
   string connectionString, DataTable territoryList)
   {
   OrdersDS functionResult = new OrdersDS();
   SqlDataAdapter da = new SqlDataAdapter(
      "up_GetOrdersByTerritory_TVP", 
   new SqlConnection(connectionString));

   da.SelectCommand.CommandType = 
      CommandType.StoredProcedure;
   da.TableMappings.Add(
      "Table", functionResult.Orders.TableName);
   da.SelectCommand.Parameters.AddWithValue(
      "@territoryList", territoryList);
   da.SelectCommand.Parameters[0].SqlDbType = 
      SqlDbType.Structured;
   da.Fill(functionResult);

   return functionResult;
}

You add the SqlParameter object to the SqlCommand using the AddWithValue method. The call to AddWithValue sets up the @territoryList parameter, passing it an instance of the strongly typed DataTable:

da.SelectCommand.Parameters.AddWithValue( 
   "@territoryList", 
   territoryList);
da.SelectCommand.Parameters[
   0].SqlDbType = SqlDbType.Structured;

The SqlParameter object representing the @territoryList stored procedure parameter must have its SqlDbType property set to SqlDbType.Structured. Structured is a new SqlDbType enumeration value -- available with version 3.5 of the .NET Framework -- that specifies structured data contained in table-valued parameters. By setting the SqlDbType to Structured, the stored procedure is instructed to expect a parameter that's a table-valued parameter.

Bump up Application Performance
So far, I've shown you how to eliminate code by applying a table-valued parameter to the solution. Next, I'll take it a step further and show you how to use TVPs to reduce the number of server roundtrips.

The sample application allows users to save their search criteria to be retrieved at a later time. Users supply a description of the search in the Saved Search Description textbox. The search criteria are saved by clicking the Save Search Criteria button. The selected territories are saved to a table in the database. Users can retrieve and run a previous search by selecting the saved search criteria from the ListBox and clicking on the Run Saved Search button. One common technique for saving the selected search criteria is to loop through the selected territories and insert each territory one at a time, by making one stored procedure call per inserted row. This isn't efficient because a roundtrip is made from the server and back each time the stored procedure is called by the client. A much more efficient technique is to use a TVP to insert all of the selected territories in one server call.

Saving selected territories with one call is accomplished using the same approach as before. This procedure is called one time by the client to save the all selected territories:

CREATE PROCEDURE 
   up_SaveTerritoryMRU (
   @territoryList TerritoryTableType 
      READONLY,
   @searchDate datetime,
   @searchDescription nvarchar(50)
)

AS

DECLARE @newTerritorySearchHistoryID int

SET NOCOUNT ON

-- insert master record
INSERT INTO Sales.TerritorySearchHistory (
   Description, SearchDate)
VALUES (@searchDescription, @searchDate)

SET @newTerritorySearchHistoryID 
   = SCOPE_IDENTITY()

-- insert most recently used 
-- territories for searches, by ID
INSERT INTO 

Sales.TerritorySearchHistoryDetails(
   TerritorySearchHistoryID, TerritoryID,
   TerritoryName)
SELECT @newTerritorySearchHistoryID, 
   t.ID, t.TerritoryName
FROM   @territoryList t

SET NOCOUNT OFF

As with the up_GetSelectedTerritories_TVP procedure, up_SaveTerritoryMRU accepts a TerritoryTableType parameter named @territoryList. As I mentioned previously, a nice attribute of UDTTs like TerritoryTableType is that they are reusable. There's no need to create a new type once it's defined; you can simply use the one created earlier. Internally, up_SaveTerritoryMRU inserts records by selecting from the @territoryList TVP:

INSERT INTO 
   Sales.TerritorySearchHistoryDetails(   
   TerritorySearchHistoryID,   
   TerritoryID, TerritoryName)
SELECT @newTerritorySearchHistoryID, 
   t.ID, t.TerritoryName
FROM @territoryList t

The client saves the selected territories to a strongly typed DataTable and passes it to the up_SaveTerritoryMRU procedure, along with the other stored procedure parameters. Using TVPs in this manner reduces the server roundtrips by packaging all selected territories into a DataTable and making one stored procedure call.

A quick look at SQL Server Profiler shows how ADO.NET executes the up_SaveTerritoryMRU procedure. The Profiler output shows that a single call is made to execute the procedure. Within that call, an instance of TerritoryTableType is declared and populated with the values passed to the @territoryList parameter through a series of INSERT statements:

declare @p1 dbo.TerritoryTableType
insert into @p1 values(3,N'Central')
insert into @p1 values(7,N'France')

exec up_GetOrdersByTerritory_TVP @territoryList=@p1

SQL Server's new table-valued parameters are a simple feature that solve a problem developers have been dealing with since the early days of database programming; how to pass multiple values as a single stored procedure parameter.

The read-only requirement is disappointing, but TVPs nonetheless solve a problem that has traditionally been a shortcoming for SQL Server. The time it takes to understand how you use them is minimal because they fit into an already familiar pattern for passing stored procedure parameters. They simplify development by eliminating unnecessary code, and have the potential to increase performance by decreasing server roundtrips.

I've already begun to think of new ways to use them, including creating a standard structure used by all of my stored procedures for passing parameters, bulk loading data from files directly into tables, or using them to pass rows of data between stored procedure calls on the server.

• Get Code Download