Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234

Faking Multidimensional Arrays in T-SQL made easy! Expand / Collapse
Author
Message
Posted Tuesday, November 04, 2008 4:46 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, January 14, 2014 1:56 PM
Points: 522, Visits: 553
The english language, she's a savage beast :)

And yes, more emoticons, MORE I TELL YOU!
Post #596984
Posted Tuesday, November 04, 2008 4:54 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, December 22, 2009 8:22 AM
Points: 696, Visits: 42
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
Post #596987
Posted Wednesday, November 05, 2008 7:09 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 22, 2012 8:02 AM
Points: 242, 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."
Post #597318
Posted Thursday, November 06, 2008 12:08 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, October 23, 2010 4:51 AM
Points: 80, 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



Post #597922
Posted Sunday, November 16, 2008 1:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 02, 2009 9:26 AM
Points: 1, 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



Post #603367
Posted Sunday, November 16, 2008 4:57 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:06 PM
Points: 35,978, Visits: 30,269
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
"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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #603390
Posted Sunday, November 16, 2008 5:32 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, January 14, 2014 1:56 PM
Points: 522, Visits: 553
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
Post #603398
Posted Monday, November 17, 2008 11:55 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 11:44 AM
Points: 561, Visits: 2,417

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
Post #603860
Posted Monday, June 01, 2009 4:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 05, 2011 10:59 PM
Points: 1, Visits: 30

Great!!! Think differently !!! Simple but powerful idea for an Arrays
Post #726523
« Prev Topic | Next Topic »

Add to briefcase «««1234

Permissions Expand / Collapse