Randal Burke (11/4/2008)
1) You do NOT have to pass tables to procedures - especially if the data is already stored on a table on disk. You don't even have to pass pointers. Everything is already there.
But then why would you want to read data from the preexisting table into another table with a composite surrogate key ('solution' 2)? And even more pressingly why would you try (unsuccessfully of course) to convert n*m values into n+m and put them into a pair of tables in such a way that the original data can be (magically) recreated by concatenation, and create an entirely redundant 'index' table (solution 1)?
The fact is sometimes you do want to pass arbitrary ordered or unordered sets of (sets of...) values between procs for one reason or another - as your article presupposes. Temp tables are unappealing due to (e.g.) recompilation issues, locking and logging overhead, etc., while inserting, selecting, deleting from permanent tables flagged with SPID (similar problems to temp tables if the calling app or its library doesn't keep a connection open) or timestamps or something is not too great either.
The only pointer I've come across in TSQL was a long while ago - to do with XPs for sharing scope between sessions (so not really TSQL anyway) ...dim memory though...
4) Show me how passing the word 'YES' (3 bytes) can be dome more efficiently using XML? Take it even further, lets pass a single bit - 0 or 1 to another process using XML and have it take up as few bytes.
No-one is suggesting wrapping everything in XML, of course.
The relevant comparison is between delimited strings and XML for passing arbitrary 'arrays' between procs. XML is indeed rather verbose, but the days when every byte is gold dust are long gone. XML has the advantage of robustly separating values from markup (bcp-ing files containing commas and double quotes ring a bell?). It also has some type checking and native support for encapsulating and extracting the data as well as transforming it using schemas which provide convenient documentation.
But whatever the ins and outs of that argument, it is between XML and delimited/fixed-width-member strings - your suggestions (even the viable one) are not in the running.
This is the concept in the real engineering world - not the concepts that are taken in the lofty world of education.
Rather presumptious...
Randal Burke
I wish there was a "head scratchin' " emoticon here...
Your article has certainly helped stimulate demand! 😉
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant