Getting Started with Adobe After Effects - Part 6: Motion Blur

Upload Image Close it
Select File

Everything SQL
Browse by Tags · View All
SQL Server 27
#SQLServer 19
SQL Server 2012 12
BRH 10
Certification 9
Training 6
SQL Azure 4
#BI 4
denali 3

Archive · View All
July 2011 11
January 2012 7
February 2012 5
March 2012 4
May 2012 3
October 2011 3
August 2011 3
November 2012 2
April 2012 2
November 2011 2

Jeff Wharton's Blog

Part 1 - Using TVP’s and XML to Transport Relational Data

Jul 2 2011 9:00PM by Jeff Wharton   


I’ve been using XML as a means of sending multiple rows of data to SQL Server ever since OpenXML was released in SQL Server 2000. Although 2000 didn’t have an XML data type, the OpenXML Rowset Provider enabled SQL Server to shred a XML string into a rowset which could then be used in Transact-SQL statements in which rowset providers such as a table, view, or the OPENROWSET function can appear. Gone were the days of looping through rows of data and sending each row to SQL Server one at a time. Just when I thought things couldn’t get any better, Microsoft released SQL Server 2005.

One of the new features of 2005 was the introduction of the native XML data type. This new type allowed users to create tables that had one or more columns of type XML in addition to relational columns, create XML variables and parameters, and use a subset of the XQuery Language to query XML data. The XML data type could also be used with the OpenXML Rowset Provider. Developers now had two easy methods of sending multiple rows of data to SQL Server.

With the release of SQL Server 2008 came Table-valued parameters (TVP). Table-valued parameters are declared by using user-defined table types and can be used to send multiple rows of data to a Transact-SQL statement or a routine, such as a stored procedure or function, without creating a temporary table or many parameters. You guessed it; another method.

The Problem

Developers can now use a number of different methods for sending multiple rows of data to SQL Server and sometimes choosing the correct method to use can be frustrating. Which one is faster? Which one is easier to implement? Can I use a mixture of both? Why isn’t there somewhere I can look for a comparison on all these technologies? These are some of the questions I’ve been asking myself over the last couple of weeks.

What’s This Post All About?

I originally thought of documenting many of the things I discovered about these technologies in a word document to be stored away and potentially lost over the coming months. I also thought about throwing it all away and sticking with my current method (OpenXML) as I found I had more question than answers and it was all starting to sound too difficult. In the end I was convinced (by a colleague) to blog about my findings.

The purpose of this series of posts is to provide other developers, DBD’s and DBA’s with an insight into the above mentioned technologies and hopefully assist them with choosing and implementing a technology (or technologies) to use within their applications. I’m also hoping that others will provide comment on their experiences with these technologies.


OpenXML: What is it and how do I use it?
XQuery: Please don’t use OpenXML!
Table-Valued Parameters (TVP’s): Table what?
OpenXML and XQuery Optimisation Tips

Final Word

Estimated post dates:

OpenXML: 27th May 2011
XQuery: 28th May 2011
Table-Valued Parameters (TVP’s): 29th May 2011
OpenXML and XQuery Optimisation Tips: 12th June 2011

Tags: SQL Server, XML, TSQL, #SQLServer, BRH,

Jeff Wharton
53 · 4% · 1171


Your Comment

Sign Up or Login to post a comment.

    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]