What is a User Defined Table Type (UDTT)?
SQL Server gives the ability to define a strongly typed object that can contain multiple rows. These objects can contain similar structs (keys, constraints, and indexes) as a table. Basically, this is an advanced reusable temp table that can be defined in other objects like stored procedures.
How will using UDTT reduce the amount of calls?
If your application requires you to save several rows at a time or need to pass in multiple Ids to pull back data, creating a UDTT will save you those round trips. For example, say you have an application that allows the user to save multiple products to a shopping cart in one click. Normally you would have to loop and call the stored procedure for every item in the list. By using a UDTT, you can pass in the entire list at once and let SQL Server handle the saves.
User Defined Table Type Example
Let’s take the above shopping cart example and write a quick demo. We will simplify the data for demonstration purposes. Typically, we would have a lot more columns representing this data and would have additional logic associated to the context of the data.
First, we will need a MemberId, ProductId, quantity and UnitPrice. Let us create the Table Type now in SQL Server with this information.
Now that the type is created, we can test it to see if it works. We need to declare a variable and assign this type as the datatype.
We have now verified the Table Type is working correctly. Lets create a table and save procedure that will have this Table Type as a parameter.
Lets test the procedure and take a look at the data in the table.
There you have it. We have successfully passed in three strongly typed rows of data with one call to the procedure in SQL Server. The procedure handled the necessary saves with little effort.
The typical application would call a stored procedure once for every row it needed to insert. By using User Defined Table Type in SQL Server, one call is made containing all the rows and passed in as a parameter. This approach limits the network overhead and creates cleaner communication between the middleware / application and database. This also makes the Network & DBA team very happy.