Simons SQL Blog

Table valued parameters along with MERGE are in my view the two killer features and the two that I believe will get most uptake when people start to use SQL 2008.

Table valued parameters enable you to send a set of data as a single parameter to your code whether it be adhoc SQL, a stored procedure or function. This is beneficial as it reduces the number of calls that have to be made to the database and so you minimise the overhead associated with each call. Imagine saving 1000 items into a table the historical way was to either, issue 1000 database calls to do the inserts or construct a single batch that consisted of all the insert statements. (This was the behaviour with ADO). Even with the latter approach where you only have 1 round trip to the database you still have the overhead associated with a single insert. I compare it to photo copying a set of pages, you can either copy each page one at a time, by lifting the lid on the copier, putting the paper on the platen and then copying and repeating for each sheet or the quicker way is to put them all in the sheet feeder and let the machine process the whole set of pages in one go.

Table valued parameters really come into their own when you have a distributed application and the speed of the link between application and database isn’t very fast. In the scenario above if the latency in the network results in a call to the db taking 10 ms (irrespective of what you are doing), then it will take 10s to save the data. Converting to a table valued parameter would reduce this down to 1 x 10ms overhead for the database call.

The MERGE statement further enhances table valued parameters by enabling you to pass a single set of data consisting of inserts, updates and deletes and save those to the database in a single statement. This has the benefit in that it simplifies code, you have a single atomic transaction and so the complexities of serialized transactions may be avoided, you get performance benefits from the fact that the index structures are being modified in a single set based approach rather than 3 individual statements performing inserts, updates and deletes. (In one test I did I found using MERGE to do purely INSERTs was quicker than the equivalent insert statement, so it is by no means a compromise).

Using AdventureWorks we have the following demo.

Firstly we need to create a TABLE TYPE for use by our Table Valued Parameter. In this case we have copied the structure of the SalesOrderDetail type.

CREATE TYPE SalesOrderDetail_Type  as TABLE (

    [SalesOrderID]          [int] NOT NULL ,

    [SalesOrderDetailID]    [int] IDENTITY(1,1) NOT NULL,

    [CarrierTrackingNumber] [nvarchar](25) NULL,

    [OrderQty]              [smallint] NOT NULL,

    [ProductID]             [int] NOT NULL,

    [SpecialOfferID]        [int] NOT NULL,

    [UnitPrice]             [money] NOT NULL,

    [UnitPriceDiscount]     [money] NOT NULL,

    [LineTotal]             AS (isnull(([UnitPrice]*((1.0)-[UnitPriceDiscount]))*[OrderQty],(0.0))),

    [rowguid]               [uniqueidentifier] ROWGUIDCOL  NOT NULL,

    [ModifiedDate]          [datetime] NOT NULL, PRIMARY KEY (SalesOrderId,SalesOrderDetailID))

GO

Secondly we create our procedure that uses the Table Type. We can then call this procedure from TSQL or client code passing in a set of data.

CREATE PROCEDURE up_OrderDetail_SaveSet

        @ChangedOrderDetail SalesOrderDetail_Type READONLY

 ,@SalesOrderID       int

AS

MERGE Sales.SalesOrderDetail OrderDetail

USING @ChangedOrderDetail    Chng        ON Chng.[SalesOrderDetailID] = OrderDetail.[SalesOrderDetailID]

                                        AND Chng.[SalesOrderID]       = OrderDetail.[SalesOrderID]

WHEN MATCHED THEN

    UPDATE SET [SalesOrderID]            = Chng.SalesOrderID           

              ,[CarrierTrackingNumber]   = Chng.CarrierTrackingNumber 

              ,[OrderQty]                = Chng.OrderQty               

              ,[ProductID]               = Chng.ProductID               

              ,[SpecialOfferID]          = Chng.SpecialOfferID           

              ,[UnitPrice]               = Chng.UnitPrice               

              ,[UnitPriceDiscount]       = Chng.UnitPriceDiscount       

              ,[rowguid]                 = Chng.rowguid               

              ,[ModifiedDate]            = Chng.ModifiedDate           

WHEN NOT MATCHED THEN

    INSERT ([SalesOrderID],[CarrierTrackingNumber],[OrderQty],[ProductID],[SpecialOfferID]

           ,[UnitPrice],[UnitPriceDiscount],[rowguid],[ModifiedDate])

     VALUES

           (Chng.SalesOrderID,Chng.CarrierTrackingNumber,Chng.OrderQty,Chng.ProductID,Chng.SpecialOfferID

           ,Chng.UnitPrice,Chng.UnitPriceDiscount,Chng.rowguid,Chng.ModifiedDate);