Reduce Database Calls with User Defined Table Types in SQL Server

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.

SQL SERVER Results

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.

SQL SERVER Results

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. 

SQL SERVER Results
SQL SERVER Results

Lets test the procedure and take a look at the data in the table.

SQL SERVER Results
SQL SERVER Results

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.

 

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