I wasn't sure the crux of your issue so I threw some code out there thinking it might initiate a discussion. I am happy to help move the ball down the field so to speak, even if I cannot always get a handle on where the boundaries of the field are 😀
Evil Kraig F (1/8/2013)
Because of this, I'd assumed there was some kind of byval functionality somewhere because .NET can pass a TVP down to a proc via a connection, but you have to include a particular library to do it. Now, my assumption (there's that word again) was that it built the type locally and then byval passed the TVP to the proc on the server. What I'm understanding from what you've said though is that it's not doing that, but is actually constructing the type on the server itself from the connection to the database, including data into it, and then passing the byref to the proc in the TVP.
I think that was a fair assumption but they chose to go a different route I guess. Giving the benefit of the doubt one could say it left room for other non-.NET implementations. The C# code sample above could easily be ported to Java and the only "plumbing" that would need to be written is the implicit conversion of the DataSet (or whatever the cousin of that is called in Java) to be iterated over and converted to the DDL needed to declare and populate the TVP prior to shipping the batch with the proc-execution over the connection to be executed. I would say that if they developed the native approach you sort of outlined using some type of serialization of the DataSet to ship down the pipe they would still want to leave the more chatty (I agree it appears chatty) method of doing the same thing for the non-.NET app clients.
That seems rather chatty to me.
Agreed. Would you say it is more chatty than passing an XML document though? How about when you factor in the need to serialize a DataSet (.NET or tabular) to XML in the code, and then the deserialize it XML into a tabular structure (XQuery or whatever) inside the proc? It would probably depend on the XML schema but it's looks to be pretty close to the same level of verbosity in general, maybe even favoring TVPs when you consider the serialization and deserialization aspects (my opinion).
On a side note... the XML datatype will not allow itself to be passed as a parameter between linked servers.
Seriously.
You have to pass a VARCHAR(MAX) then shove it into a local XML variable at the target. Microsoft, your inter-server communications decisions continue to boggle me.
I can't say I have run into that one in my own development efforts but have seen some folks on SSC knock Linked Servers for having limitations around XML types. Passing them in is not the only one either. There is a nasty limitation surrounding retrieving data from columns with XML columns too. It has been that way since the XML type was introduced and they have ignored requests to streamline it.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato