Table Valued Parameters

In recent times I haven’t written any complex TSQL code which needed to scale, possible a scenario you may come across while working on a reporting application where most of the processing is done on the database server using TSQL. In SQL 2005 user defined types were available but in Katmai this has been furthered to allow table valued parameters to be passed to stored procedures. Well we can also think of them as instances of user defined types. At the end of the day it boils down to being able to efficient and more performant code , but then this feature seems to have a limitation at the moment.

The table data type allows variables 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. That's great, but don't temporary tables do the same? Yes and No …. While table variables and temporary tables are similar, there are several key differences that determine which option you use.
E.g. Consider the sample below in SQL 2005 to add orders to the database table using TSQL, I will need a stored procedure which takes each column in a row as a parameter, and more over each row needs a round trip to the database server..

CREATE PROC sp_add_order(@order_id int, @customer_id int, @order_total money )
AS
INSERT INTO Order
(
order_id,
customer_id,
order_total
)
VALUES
(
@order_id,
@customer_id,
@order_total
)
GO

Now with SQL 2008 we are able to insert several rows of data with just one round trip to the server by using a user defined type called OrderType which has a table structure. That is efficient and quite performant…

CREATE TYPE OrderType AS TABLE
(
[order_id] int NOT NULL,
customer_id int NOT NULL,
[OrderTotal] [money] NULL
)
GO

ALTER PROC sp_new_order (@order OrderType READONLY)
AS
INSERT INTO [OrderTest]
(
order_id,
customer_id,
[OrderTotal]
)
SELECT * FROM @order

RETURN
GO

The one limitation to note here is the READONLY option on the parameter, At the moment the CTP only allows us to pass read-only table valued parameters, We are unable to use OUT parameters of Table Value Type, It would be great to be able to do this, as we don’t have to make nested calls to stored procedures in reporting scenarios… It is to be seen how this pans out in the final release, although the feature doesn’t look big, the sheer benefits of being able to use Table Valued Parameters comes as blessing in disguise for many TSQL developers.