SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Faking Multidimensional Arrays in T-SQL made easy!


Faking Multidimensional Arrays in T-SQL made easy!

Author
Message
David in .AU
David in .AU
Right there with Babe
Right there with Babe (798 reputation)Right there with Babe (798 reputation)Right there with Babe (798 reputation)Right there with Babe (798 reputation)Right there with Babe (798 reputation)Right there with Babe (798 reputation)Right there with Babe (798 reputation)Right there with Babe (798 reputation)

Group: General Forum Members
Points: 798 Visits: 561
The english language, she's a savage beast Smile

And yes, more emoticons, MORE I TELL YOU!
stax68
stax68
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2759 Visits: 44
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! Wink

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
Timothy J Hartford
Timothy J Hartford
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1010 Visits: 502
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."
johncyriac
johncyriac
Old Hand
Old Hand (308 reputation)Old Hand (308 reputation)Old Hand (308 reputation)Old Hand (308 reputation)Old Hand (308 reputation)Old Hand (308 reputation)Old Hand (308 reputation)Old Hand (308 reputation)

Group: General Forum Members
Points: 308 Visits: 58
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 71 93

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



zpq4
zpq4
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 9
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)

Group: General Forum Members
Points: 216402 Visits: 41986
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. Smile

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
David in .AU
David in .AU
Right there with Babe
Right there with Babe (798 reputation)Right there with Babe (798 reputation)Right there with Babe (798 reputation)Right there with Babe (798 reputation)Right there with Babe (798 reputation)Right there with Babe (798 reputation)Right there with Babe (798 reputation)Right there with Babe (798 reputation)

Group: General Forum Members
Points: 798 Visits: 561
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 Smile

-- End Waffle
Phil Factor
Phil Factor
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4884 Visits: 3031

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.


It's a little bit esoteric, but Robyn and I hope that someone finds it useful!




Best wishes,

Phil Factor
Simple Talk
cmmurugan
cmmurugan
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 32
Great!!! Think differently !!! Simple but powerful idea for an Arrays
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search