Table-Valued Parameters in SQL Server

Posted by Al Tenhundfeld on Aug 26, 2008

Sections

Development,

Operations & Infrastructure

Topics

SQL Server ,

.NET Framework ,

.NET

Tags

ADO.NET ,

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

1. Back to top

   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.

   Reply

2. Back to top

   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.

   Reply

3. Back to top

   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.

   Reply

4. Back to top

   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.

   Reply

5. Back to top

   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.

   Reply

6. Back to top

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

   Reply

7. Back to top

   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.

   Reply

• Older >