Since the release of SQL Server 2000, we have had the table data type at our disposal. But, its major weakness is that it could not be passed in or out of a stored procedure as a parameter. Well, Microsoft has heard and answered the cries of database administrators and developers. SQL Server 2008, which Microsoft will RTM by the end of Q2 2008, comes complete with the ability to pass variables of table data types as parameters. The new stored procedure feature, known as table-valued parameters, can save cycles wasted by making excessive calls to SQL Server and save the overhead incurred by performing extra writes against the database.
In this tip, we'll take a look at how to put table-valued parameters to work in your stored procedures; but first you must understand exactly what the table data type is and how it works.
The table data type allows for variables in your code that are, in effect, tables. They also allow you to return tabular data from functions without doing a SELECT at the end of the function. You might be thinking, "That's great, but didn't we already have temporary tables that do the same thing?" The answer to that question is yes and no. While table variables and temporary tables are similar, in that both allow you to create a temporary table structure for storing information, there are several key differences that determine which option you use.
Temp tables vs. table variables in SQL Server
Temp tables are stored physically on disk in tempdb, while table variables are stored partially in memory and partially on disk. This difference means a few things for your code. Since table variables are at least partially in memory, they can be faster than temp tables. Temp tables, however, are on disk and look just like real tables; therefore you can create indexes on them, a task not possible with a table variable (beyond a primary key).
Additionally, you can create global temp tables that are accessible by other processes, another function that cannot be done with table variables. Which one you use will depend on your situation. By no means have table variables replaced temporary tables. Each still has a purpose and a use. Now that you understand where table variables fit into the big picture, let's take a look at the new feature in SQL Server 2008 that promises to increase the efficiency of complex code all over the globe -- table-valued parameters.
With table-valued parameters, seemingly ordinary stored procedures are waking up to discover they have extraordinary abilities. They can now accept a table in the form of a variable as a parameter, a feat previously not possible. So what does this mean to you? To answer that question, let's review the common example of an order processing database. The database contains three tables that we care most about: order, order detail and customer.
When an application writes to the database, how many inserts are necessary to insert a complete order? The tempting answer is three, one for each table. But the real answer is at least two and up to a virtually unlimited maximum. Think about it, you need to add a record for the customer only if the customer doesn't already exist, a record for the order, and at least one record for order detail. Order details is where things get sticky. There will be one record for each item ordered, so if the customer orders 50 items, then there will be 50 records.
To do this kind of processing with stored procedures, you would traditionally have three procedures: one to add or update the customer, one to add the order and one to add the details. Here is what those procedures would look like (the procedures below do not have all the parameters you would expect to see. Rather, these have been kept small for demonstration purposes).
(Click on image for enlarged view.)
Click to see the stored procedure in a downloadable document.
These three procedures will allow you to insert all the detail about your customer, order and order details. This is where you can use table-valued parameters to improve the procedure efficiency and minimize the round trips to the database. In this scenario, an order with nine items requires 11 stored procedure calls, and it's likely that means 11 separate trips from code to SQL Server. If you change to your table-valued parameters, this number is reduced to two. Yes, just two calls to SQL Server will be all you need to add all of your order information. Look at the following stored procedures that you'll use to replace sp_add_order and sp_add_order_detail.
(Click on image for enlarged view.)
Click to see the stored procedure in a downloadable document.
See how much cleaner this option is? You insert into the Order and OrderDetail tables directly from a select of the data in the @order and @order_detail parameters.
There is one last loose thread you need to cut. You probably noticed in the last stored procedure that parameters for @order and @order_datail have data types of OrderType and OrderDetailType. I'll bet you are wondering how this translates to table data types. These two types are user defined and were set up before we create the procedure. All you have to do is use the CREATE TYPE syntax as follows.
Click to see the stored procedure in a downloadable document.
As you can see, this creates two user defined types that are set up as the table data type. You can now type our parameters with these UDTs and pass table variables as parameters. This works for both stored procedures and user-defined functions.
Next time you find yourself in a spot where you need to pass large numbers of parameters -- especially if those parameters relate directly to a physical table -- you may find that table-valued parameters are the way to go.
| ABOUT THE AUTHOR: |
|
Eric Johnson (MCSE, MCITP: Database Administrator, MCSD) is a co-founder of Consortio Services and the primary Database Technologies Consultant. Eric, a SQL Server MVP, has delivered numerous SQL Server training classes and webcasts as well as presentations at national technology conferences. He has presented at TechMentor on SQL Server 2005 Replication, Reporting Services, and Integration Services. Eric is active in the local SQL Server Community, serving as the president of the Colorado Springs SQL Server Users Group.
Copyright 2007 TechTarget
|
|
