November 4, 2008 at 4:46 pm
The english language, she's a savage beast 🙂
And yes, more emoticons, MORE I TELL YOU!
November 4, 2008 at 4:54 pm
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
November 5, 2008 at 7:09 am
Just my (albeit late) 2 cents:
Beyond the obvious "tables are better than arrays" it sometimes helps to stop and rethink about the root technology that was used to develop what we currently have. It improves your understanding and therefore your ability to use a tool. An "X,Y, Value" table is somewhat obvious for an "array", but looses a lot of value that a table may have... and yet if you stop and think about it the true advantage of an array is that you can reference or even move an array by moving a simple pointer. You can reorder the array by only reordering the x axis (because the index is not the order). So, depending on why you want an "array" in T-SQL, thinking about the advantages of arrays will help you design better tables, especially when you think of a table of X, a table of Y, and a table of XY Values.
Besides, if you don't see any advantage in learning something, even if it's just how someone else tears down a problem, why are you in IT?
"I have not problem being wrong. At least then I have the opportunity to learn something new."
November 6, 2008 at 12:08 am
Wow!
What a lengthy discussion, It’s the time to close down this forum , normally dimension is s property of the element
Example like
Location Latitude longitude
(Delhi)India 7193
And Table is much more than an array ,
Example each column (in a primary key ) is a dimension with proper tag or name , and more than one element can be attached to each dimension ,that is the columns other than primary key
A modern Data base is far away from ,BASIC and FORTRAN , and there is much difference in Compiler design and Database Programming.
To the maximum a database programmer may go up to the SET theory and not below that,
Too much of information will lead to disaster
regards
john
November 16, 2008 at 1:35 pm
All,
It seems to me that the need to fake arrays in T-SQL has a lot in common with the need to post bail; sure in a crisis you have to do what you have to do, but afterwards you should sit down and really ask yourself “What are the choices that I have made in the past days, weeks, etc that brought me to this terrible place?”
I realize that different environments require different approaches to how solutions are implemented. So I’m sure I’m missing something. Anyone care to enlighten me?
Thanks
November 16, 2008 at 4:57 pm
zpq4 (11/16/2008)
So I’m sure I’m missing something. Anyone care to enlighten me?
Heh... not unless you've ever had to pass an array from a GUI to the database. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
November 16, 2008 at 5:32 pm
I can understand your confusion, some of the comments and examples are referring to faking an array for data that already exists in the database, which I do have to admit does sound horribly redundant (I'm sure there are times when you might want to do this, but as you mentioned, have a good think about why you are needing to do that).
And although there are times when knowing about the original way something was done does indeed help your present knowledge about why things work, there are times when it can hinder, just because things used to be done a certain way doesn't mean they have any relevance to how things are done now; indeed knowing the way things used to be done may actually be a way of blocking the creative juices from coming up with a better way.
"It Depends" is the standard answer for most things in the database world; I tend to ask "Why?" first, when why has been answered, then it depends 🙂
-- End Waffle
November 17, 2008 at 11:55 am
[p]I had a certain sympathy with this article in its nostalgic harking-back to the days of the old BASIC arrays. (actually, very few BASIC implementations got as far as Matrix Mathematics, or any other really useful stuff). I started to write a forum entry to maybe describe a few ways I tackle the sort of problem that requires arrays and matrices. (Mostly statistical stuff, which is nowadays given to you as part of the BI tools but it provides a way of solving all sorts of odd problems in SQL). Then Robyn got involved, it all escalated, got too big for a forum entry, and we ended up developing it into a workbench which we've now posted on Simple-Talk as the SQL Server Matrix Workbench.[/url][/p]
[p]It's a little bit esoteric, but Robyn and I hope that someone finds it useful![/p]
Best wishes,
Phil Factor
June 1, 2009 at 4:42 am
Great!!! Think differently !!! Simple but powerful idea for an Arrays
Viewing 9 posts - 31 through 38 (of 38 total)
You must be logged in to reply to this topic. Login to reply