User Defined Table Types have been in SQL Server since 2008. They are mostly used to pass sets of data into a stored procedure. You create the user defined table type first in a database. Then you can populate the type from your client application or another SQL Server object. That table, defined as your own table type, may then be used much as any other table in a database.
When used as input parameters for a stored procedure then the contents of the table is read only. That means you cannot add/delete or modify any of the rows that instance contains.
In ADO.Net you can specify a collection of data as the input to a stored procedure parameter defined as a user defined table type. For this reason, user defined table types are database specific and cannot be shared programmatically across multiple databases. Even if the type is exactly the same, you cannot send data to a database from which the table type was not defined.
You can emulate the table type by creating a temporary table and populating it prior to calling a stored procedure, or even dynamic SQL. I sometimes create a temporary table and populate it using a Bulk Copy ADO command. Then I can execute any other SQL I like utilizing the contents of the temp table as long as it is executed in the same ADO connection.
I also like to use User Defined Table Types to pass information from one stored procedure to another. While I can still do the same thing with temporary tables, the user defined table types are clearer, and may be coded successfully without having to first create a temporary table in order to get the stored procedure to compile.
Are you using User Defined Table types? Share your experience by email to btaylor@sswug.org, or simply leave your comments here.
Cheers,
Ben