How Would You Use Table Valued Parameters (TVP)?

How Would You Use Table Valued Parameters (TVP)?

Rate This
  • Comments 17

Many of you have asked for Table Valued Parameters (TVP) support in the Microsoft JDBC Driver for SQL Server.  We have added TVP support to our roadmap and are gathering requirements (we don’t have an availability date yet). 

 

We would like to hear more details about how you would use TVP’s and how TVP’s will benefit your organization.  Many of you have told us that you currently pass structured data to SQL Server/SQL Azure using XML but we would like more
specifics. 

 

Can you share snippets or samples of your existing code that you would like to retool to use TVP’s? Can you provide more details on how TVP’s will help you or your organization?

 

You can email us or comment on this blog.

 

Thanks,

Shamitha Reddy

Program Manager – Microsoft JDBC Driver for SQL Server

 

For technical support, please use our forums.

Leave a Comment
  • Please add 3 and 6 and type the answer here:
  • Post
  • Hi,

    In one of my previous implementations using Oracle varrays, we sent a whole table's worth of data of data to a procedure that performed the inserts/updates. The call itself was for performance reasons and to reduce the amount of iterations between the app server and the database.

    If we can use it similarly with TVP in SQL Server 2008 - it would be great!

    I have been primarily searching for a hibernate solution and as a last resort with pure jdbc drivers. If you can help me with a work around in hibernate, that will be great too.

    Thanks,

    Lionel

  • is it possible to subscribe to a notification list for when this feature comes out?  or should we watch this blog?

    TVP usage:

    - complex batch insert and update as part of a stored procedure in single round-trip (i.e.: update t set t.c = tvp.c FROM t2 INNER JOIN tvp ON t.flag = t2.flag)

    - inner join of data against some table to match properties (i.e.: tvp row data is {guid, bitflag}; inner join on some table and do some boolean magic with bitflag)

    - so we don't have to use "col IN (blah)" with regular TSQL

    Frank

  • @Frank - Thanks for the examples, that is very helpful.  We generally publish a blog post with each release of the Microsoft JDBC Driver that describes the major features included in that release; so if you subscribe to an RSS feed for our blog you should be notified.  While TVP is on our roadmap we don't have a firm date of when this feature might be available.

  • Hello. I wrote an article for SQL Server Central a few years ago about using TVPs to import data quickly while using very little memory:

    www.sqlservercentral.com/.../66554

    The example in the article is rather simplistic but I am in the process of updating the article with more real-world test cases.

    To answer your question more directly:

    My company has several servers and most of them are still running SQL Server 2005 while a few are on SQL Server 2008. Yes, we are in the process of upgrading everything to SQL Server 2012 but for now I still have to deal with the current situation.

    We have quite a few processes that import bulk data (a few thousand rows up to many hundreds of thousands of rows) for many customers across many functional areas, all throughout the day. In order to enhance the scalability of system, we are starting to move more of these operations to a batch model where the applications send in as much information as possible for a set of data (100 - 200 or even 1000 rows, depending on the complexity of the operation and tables involved) and the stored procedure does what is a multi-step merge (this will be detailed in my updated article).

    Currently I am using XML to pass in a "table" of data (the 100 - 200 rows) for a SQL Server 2005 process. The XML is just a transport and is shredded into a temporary table so the data can be used efficiently. I would love to be able to use TVPs instead. At the very least I would have strong datatypes with the TVP as opposed to converting everything to string for the XML and then back to real datatypes again in the temporary table. Having strong datatypes in the transport cuts down on potential data errors. The streaming option is a bonus for helping to keep app server memory usage low. And converting the current XML transport to TVP would be trivial on both app and DB sides.

    I mention all of this because we have another application that is Java-based and connecting to SQL Server 2008 and would benefit from TVP use but clearly that is not an option.

    Take care,

    Solomon...

  • Very helpful post

  • The primary way we would use TVPs is as a way to bulk load multiple tables that need to be inserted/updated together as part of a single transaction.  While this is possible in C#, and we love it, our primary enterprise products are Java-based.  As such, we currently use csv data which we then process with a SQLCLR TVF to convert into a set of TVPs that we process of of.  In testing, this solution uses 300% more database CPU than direct TVPs from C# do.

    Please implement this feature.  The lack of TVPs from Java have led us to use Oracle at our largest deployment sites since JDBC does support Oracle types.

  • Currently we have a mixed envronment and are using a JAVA based enterprise service bus for some of our larger transaction based applications. We have a sql server that holds a canonical format and are trying to use the JAVA ESB to insert a XML based canonical message into the sql server. From the web and client side we are using TVP so that our entity can do a direct insert with a transactional sproc instead of using an ADO transaction and worrying about the identiy.

    For us to have the use of TVP's from the service bus would help streamline the data insertion and keep our code to one consistante structure meaning one stored procedure to insert a canonical message.

  • We currently have to move data processed by our Java servers in bulk via XML.  Which we then have to tear down in SQL Server (which is slow) and manipulate/insert into out SQL Server.  TVP would give us great performance improvements (as it did in C# -> SQL Server at my previous job).  It decreases the size of data over the wire and decreases process time for transactions that need to be fast and deal with bulk data.

    We have a new project that is coming up and the fact that TVP is unavailable is causing me great stress in determining what ETL tool to use for it, when it should be simple for me to just pick out existing ETL tool and move things with TVP.

  • When are we expected to see a first life of a TVP implementation?

  • Hi,

    Is there any news regarding a release of JDBC with TVP support? If you are still after examples of how this may be used then please let me know, and I will email you details of our pending implementation.

    Thanks,

    Darren

  • This is ridiculous. TVPs are available within C# so all the use cases for that would apply for JDBC.

  • When release JDBC support for TVPs?. That is becoming a dire need for our new developments based on java and SQLServer

  • I was going to implement TVP support in jOOQ (http://www.jooq.org). But then I saw this and thought... Nope.

  • No Update From the JDBC Team... This is need for the JAVA & SQL based developers.!! The same concept available in C# and not in JDBC and  developers need more than 2 years to give the tentative release date.

    This is horrible!!! Great Developers !!

  • Are there any updates on inclusion of this feature?

Page 1 of 2 (17 items) 12