BT
x Your opinion matters! Please fill in the InfoQ Survey about your reading habits!

Table-Valued Parameters in SQL Server

by Al Tenhundfeld on Aug 26, 2008 |
Table-valued parameters are a new feature introduced in SQL Server 2008. They provide a built-in way to send multiple rows of data from a client application to SQL Server in a single parameterized SQL statement.

The foundation of this functionality rests on the new User-Defined Table Types in SQL Server 2008. This allows users to register table definitions as well-known types. These table types can be used as local variables in batches or in the body of stored procedures, much like a strongly-typed version of the generic table variable available in previous versions of SQL Server. However, unlike the generic table variables, user defined table types can also be used as parameters for stored procedures or parameterized TSQL.

User-defined table type example

User-defined table types have numerous restrictions:

  • A user-defined table type cannot be used as a column in a table or a field in a structured user-defined type.
  • A nonclustered index cannot be created on a user-defined table type unless the index is the result of creating a PRIMARY KEY or UNIQUE constraint on the user-defined table type.
  • A DEFAULT value cannot be specified in the definition of a user-defined table type.
  • The user-defined table type definition cannot be modified after it is created.
  • User-defined functions cannot be called within the definition of computed columns of a user-defined table type.
  • A user-defined table type cannot be used as a table-valued parameter for user-defined functions.
User-defined table types have a further limitation when used as table-valued parameters; they are read-only within the parameterized statement or procedure:
You cannot update the column values in the rows of a table-valued parameter and you cannot insert or delete rows. To modify the data that is passed to a stored procedure or parameterized statement in table-valued parameter, you must insert the data into a temporary table or into a table variable.
User-defined table types can be used within ADO.NET by utilizing the standard SqlParameter type:
  • The TypeName of the parameter must be set to the name of the user-defined data type, e.g., "dbo.PersonInfo"
  • The SqlDbType must be set to SqldbType.Structured
  • The Value for the parameter must represent data that matches the user-defined data type. System.Data.SqlClient supports table-valued parameters from a System.Data.DataTable or IList. Additionally, rows of data can be streamed to a table-valued parameter using System.Data.Common.DbDataReader or any derived classes, e.g., OracleDataReader.
Before table-valued parameters, developers had several options for mimicking this ability:
  • Use a series of individual parameters to represent the values in multiple columns and rows of data. The amount of data that can be passed by using this method is limited by the number of parameters allowed. SQL Server procedures can have, at most, 2100 parameters. Server-side logic is required to assemble these individual values into a table variable or a temporary table for processing.
  • Bundle multiple data values into delimited strings or XML documents and then pass those text values to a procedure or statement. This requires the procedure or statement to include the logic necessary for validating the data structures and unbundling the values.
  • Create a series of individual SQL statements for data modifications that affect multiple rows, such as those created by calling the Update method of a SqlDataAdapter. Changes can be submitted to the server individually or batched into groups. However, even when submitted in batches that contain multiple statements, each statement is executed separately on the server.
  • Use the bcp utility program or the SqlBulkCopy object to load many rows of data into a table. Although this technique is very efficient, it does not support server-side processing unless the data is loaded into a temporary table or table variable.

Hello stranger!

You need to Register an InfoQ account or or login to post comments. But there's so much more behind being registered.

Get the most out of the InfoQ experience.

Tell us what you think

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

TypeName appears to be optional by Ben Amada

I noticed that not setting any TypeName in ADO.NET doesn't raise an error and the stored procedure seems to run fine.

Re: TypeName appears to be optional by Al Tenhundfeld

Interesting. I hadn't even thought to try that, but I've only used this feature in a couple of prototypes. I wonder what ADO.NET/SQL Server is doing to determine that the parameter value (the DataTable) is valid for the user-defined table type. And I wonder how much freedom that gives you. For example, if the DataTable had several extra columns not defined in the table type, would SQL Server still accept it?



One of my concerns is that this approach requires the data access code in .NET to know a lot about the user-defined table type. Have you given much thought to how this feature will fit into a real-world application?


If I needed to start using this immediately, I would probably write a code generator to create a model class for each user-defined table type and include a static method in the model that had the intelligence to convert a list of itself to a DataTable. If you used active generation, i.e., made that model codegen part of your build process, you would at least get some of the benefits of a strongly-typed environment.



It will definitely be worth watching the ORM space to see if NHibernate, LINQ to SQL, or any of the others include anything to address this new capability.

Re: TypeName appears to be optional by Al Tenhundfeld

I forgot to thank you for sharing that. I really appreciate it when people add insightful comments to my news posts. So thanks, Ben.

Re: TypeName appears to be optional by Francois Ward

type and include a static method in the model that had the intelligence to convert a list of itself to a DataTable


Why convert it to a datatable? Unless i'm missing something, the article stated that an IList could do the trick too. So i'm guessing an IList of a domain object with matching properties should work? If so, no conversion should be needed.

Re: TypeName appears to be optional by Al Tenhundfeld


Why convert it to a datatable? Unless i'm missing something, the article stated that an IList could do the trick too. So i'm guessing an IList of a domain object with matching properties should work? If so, no conversion should be needed.

Yeah, that's a fair point. I actually couldn't get the IList approach to work, but I included it because documentation said it was possible. I read on a non-MS source that ADO.NET will only accept IList of Microsoft.SqlServer.Server.SqlDataRecord, but I haven't taken the time to research the validity of that.

Re: TypeName appears to be optional by Charles Verdon

What about a custom implementation of DbDataReader to 'stream' the entities using reflection or an ORM's mapping engine? That would be cleaner and more performant than converting to a DataTable...

Re: TypeName appears to be optional by Al Tenhundfeld

What about a custom implementation of DbDataReader to 'stream' the entities using reflection or an ORM's mapping engine? That would be cleaner and more performant than converting to a DataTable...

That sounds good to me. I guess my only reservation is that I don't know how much work is involved in writing a custom implementation of DbDataReader. IDataReader, which extends IDisposable and more importantly IDataRecord, is a pretty bulky interface and would take some time to write well, in my opinion.


Without delving into the details, I agree that the approach you outline is better in the long-term, but I still think that if I had to very quickly spin up a framework using table-valued parameters, I'd probably start with using a DataTable as the mapper. In the code I'm envisioning, the models and translation code would be generated anyway; so if we wanted to write the custom DbDataReader implementation at a later date, we could easily plug that code into the generator and regen all of the models.



Also, Ben's point about not needing to set the TypeName on the SqlParameter is true for stored procedures only. If you're using a simple parameterized SQL statement, you'll still need to specify the TypeName.

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

7 Discuss

Educational Content

General Feedback
Bugs
Advertising
Editorial
InfoQ.com and all content copyright © 2006-2014 C4Media Inc. InfoQ.com hosted at Contegix, the best ISP we've ever worked with.
Privacy policy
BT