Partager via

How Would You Use Table Valued Parameters (TVP)?

  • Article

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.

Comments

  • Anonymous
    April 11, 2012
    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

  • Anonymous
    May 08, 2012
    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

  • Anonymous
    May 08, 2012
    @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.

  • Anonymous
    May 27, 2012
    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...

  • Anonymous
    September 20, 2012
    Very helpful post

  • Anonymous
    October 25, 2012
    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.

  • Anonymous
    January 22, 2013
    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.

  • Anonymous
    March 20, 2013
    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.

  • Anonymous
    May 07, 2013
    When are we expected to see a first life of a TVP implementation?

  • Anonymous
    August 29, 2013
    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

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

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

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

  • Anonymous
    June 10, 2014
    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 !!

  • Anonymous
    July 01, 2014
    Are there any updates on inclusion of this feature?

  • Anonymous
    July 22, 2014
    Seriously, this has not been addressed for over two years? This is a major shortfall. Please update with any news.

  • Anonymous
    August 12, 2014
    Still waiting with baited breath.... any body home?

  • Anonymous
    November 13, 2014
    blogs.technet.com/.../preview-release-of-the-sql-server-jdbc-driver.aspx

  • Anonymous
    June 17, 2015
    I currently use xml to pass multiple rows to the database, but only because jdbc doesn't have TVP support. I suspect that the uses for TVP would be identical to the ones that resulted in it's inclusion in  ODBC.

  • Anonymous
    July 14, 2015
    The comment has been removed

  • Anonymous
    December 21, 2015
    Hi, Can we pass TVP to MS SQL stored procedure from java

  • Anonymous
    December 21, 2015
    I mean, Is JDBC with TVP support is available now? Regards, Rahul