opc.three (1/8/2013)
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 😀
Fair enough. When I get frustrated my Technology to English skills tend to get a bit wonky. :hehe:
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).
Oh, the TVP is probably less chatty, but still, the connection is almost dynamic in the way it's passing data that way instead of single call-response. One of the things I press on my front end developers to do is to make sure that connection pooling is most effeciently utilized. Something like this, where you have to build your type on the connection THEN pass it to the proc is less efficient as far as I can tell, particularly since you pretty much have to dedicate that connection to that instance of the process, similar to externally controlled transactions.
In that way, I actually consider XML (within reason) to be a better choice.
There is a nasty limitation surrounding retrieving data from columns with XML columns too.[/url][/u] It has been that way since the XML type was introduced and they have ignored requests to streamline it.
Yeah, I've seen that. It's wierd. I can pass around VARCHAR(MAX) and VARBINARY(MAX) all day long on a linked server, but XML chokes it. Makes no sense. It's like Microsoft said "Here, fine, shut up. Here's some XML. Now GO AWAY." "Daddy it doesn't work." "Not my problem, you got your pony. GO AWAY."
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA