SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Randal Burke / Faking Multidimensional Arrays in T-SQL made easy! / Latest Posts

RE: Faking Multidimensional Arrays in T-SQL made easy!

cmmurugan — Mon, 01 Jun 2009 04:42:44 GMT

Great!!! Think differently !!! Simple but powerful idea for an Arrays

RE: Faking Multidimensional Arrays in T-SQL made easy!

Phil Factor — Mon, 17 Nov 2008 11:55:29 GMT

[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[url=http://www.simple-talk.com/sql/t-sql-programming/sql-server-matrix-workbench/] SQL Server Matrix Workbench.[/url][/p][p]It's a little bit esoteric, but Robyn and I hope that someone finds it useful![/p]

RE: Faking Multidimensional Arrays in T-SQL made easy!

David in .AU — Sun, 16 Nov 2008 17:32:25 GMT

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

RE: Faking Multidimensional Arrays in T-SQL made easy!

Jeff Moden — Sun, 16 Nov 2008 16:57:57 GMT

[quote][b]zpq4 (11/16/2008)[/b][hr]So I’m sure I’m missing something. Anyone care to enlighten me? [/quote]Heh... not unless you've ever had to pass an array from a GUI to the database. :)

RE: Faking Multidimensional Arrays in T-SQL made easy!

zpq4 — Sun, 16 Nov 2008 13:35:14 GMT

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

RE: Faking Multidimensional Arrays in T-SQL made easy!

johncyriac — Thu, 06 Nov 2008 00:08:10 GMT

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, [quote]Too much of information will lead to disaster[/quote]regardsjohn

RE: Faking Multidimensional Arrays in T-SQL made easy!

Timothy J Hartford — Wed, 05 Nov 2008 07:09:25 GMT

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."

RE: Faking Multidimensional Arrays in T-SQL made easy!

stax68 — Tue, 04 Nov 2008 16:54:09 GMT

[quote][b]Randal Burke (11/4/2008)[/b][hr]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.[/quote]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...[quote]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.[/quote]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. [quote]This is the concept in the real engineering world - not the concepts that are taken in the lofty world of education.[/quote]Rather presumptious...[quote]Randal BurkeI wish there was a "head scratchin' " emoticon here...[/quote]Your article has certainly helped stimulate demand! ;)

RE: Faking Multidimensional Arrays in T-SQL made easy!

David in .AU — Tue, 04 Nov 2008 16:46:21 GMT

The english language, she's a savage beast :)And yes, more emoticons, MORE I TELL YOU!

RE: Faking Multidimensional Arrays in T-SQL made easy!

stax68 — Tue, 04 Nov 2008 16:36:28 GMT

[quote][b]David B (11/4/2008)[/b][hr][quote]I read it the first time. You seemed to say (and therefore like it or not did say) it was about 3D arrays, but the author didn't go far enough in his explanation.[/quote]From what I read of Jeff's comment it was the other way around, that the article didnt go far enough and should have included 3D arrays to further explain the process.And is there a full moon about somewhere, there seems to be so many agressive comments about this post...[/quote]Well, OK but 'far enough in Xing' to me means Xing, but not far enough.Re aggression - lots of criticism, certainly, along with a bit of robust badinage between Mr Moden and myself. Perhaps more emoticons needed to add some passivity, eh? ;)

RE: Faking Multidimensional Arrays in T-SQL made easy!

Jeff Moden — Tue, 04 Nov 2008 16:14:25 GMT

Spot-on, David.

RE: Faking Multidimensional Arrays in T-SQL made easy!

David in .AU — Tue, 04 Nov 2008 16:05:06 GMT

[quote]I read it the first time. You seemed to say (and therefore like it or not did say) it was about 3D arrays, but the author didn't go far enough in his explanation.[/quote]From what I read of Jeff's comment it was the other way around, that the article didnt go far enough and should have included 3D arrays to further explain the process.And is there a full moon about somewhere, there seems to be so many agressive comments about this post...

RE: Faking Multidimensional Arrays in T-SQL made easy!

Lynn Pettis — Tue, 04 Nov 2008 15:35:21 GMT

Here is the actual quote from movie:[quote]He is intelligent, but not experienced. His pattern indicates 2 dimensional thinking. -- Spock[/quote]

RE: Faking Multidimensional Arrays in T-SQL made easy!

Jeff Moden — Tue, 04 Nov 2008 15:30:31 GMT

[quote][b]stax68 (11/4/2008)[/b][hr][quote][b]Jeff Moden (11/4/2008)[/b][hr]Keep it clean, slick. ;) [/quote]Oh, I do - mens sana in corpore sano old chap.[quote] And, don't blame me if you don't understand what I'm saying. :w00t:[/quote]in that case may I congratulate you on your magnificent imperial robe![/quote]Wow... tough crowd, Tim. ;)

RE: Faking Multidimensional Arrays in T-SQL made easy!

Lynn Pettis — Tue, 04 Nov 2008 15:29:07 GMT

[quote][b]Randal Burke (11/4/2008)[/b][hr]2) Captain Kirk once said, in his final battle with Khan - "He is evidencing classic two dimensional thinking" - we all evidence that quite often. [/quote]I would have sworn that it was Spock who made this comment...

RE: Faking Multidimensional Arrays in T-SQL made easy!

ians — Tue, 04 Nov 2008 14:33:24 GMT

[quote][b]mmcclure (11/4/2008)[/b][hr]Okay, corrected the code for SQL 2008 (at least this is what worked for me)./* create the basic table structures needed */CREATE TABLE [X_DIMENSION] ( [LOC] [int] NULL , [X_ELEMENT] [varchar] (2000) NULL )CREATE TABLE [Y_DIMENSION] ( [LOC] [int] NULL, [Y_ELEMENT] [varchar] (2000) NULL )CREATE TABLE [X_Y_INDEX] ( X_LOC [int] NULL, Y_LOC [int] NULL )/* Now we create some data to place into the tables, indexed */INSERT X_Y_INDEX (X_LOC, Y_LOC)SELECT 5,7INSERT X_DIMENSION (LOC,X_ELEMENT)SELECT 5,'DATA IN ELEMENT 5 'INSERT Y_DIMENSION (LOC,Y_ELEMENT)SELECT 7,'REMAINING DATA FOR ELEMENT 5,7'DECLARE @X INT, @Y INTSET @X = 5 -- or whatever method of loading you wantSET @Y = 7SELECT A.X_ELEMENT + B.Y_ELEMENT FROM X_DIMENSION A, Y_DIMENSION B WHERE A.LOC = @X AND B.LOC = @Y/* The Query returns the concatenated value! *//* DATA IN ELEMENT 5, REMAINING DATA IN ELEMENT 7*/[/quote]Still pointless. Why use the X_Y_INDEX table, your not using it as part of the retreive, so why have it.If you think this all works, show us how you would store a second independant, seperate item in element (5,8).I'm astounded this got past any approval/editing process.

RE: Faking Multidimensional Arrays in T-SQL made easy!

Randal Burke — Tue, 04 Nov 2008 13:42:41 GMT

[quote][b]Joshua Perry (11/4/2008)[/b][hr]This is great. Going back to using the concept of a stack and heap. This is way faster than using a cursor to pivot data. Now if you could just find a way to run SQL Server on a MOS 6502...[/quote]Well,Since you asked... the MOS6502 WAS able to run a cross-assembled version of CP/M, from which DOS was built. Then Windows 1 was first brought out on an Apple //e (without using the Z80 card) - then came the Microsoft Z-80 card with Windows 3.0... and now we have advanced all the way up to having Windows Compact which runs on... OMG!!! those pesky little microprocessors with only a few megs of program space and then those wonderful little plug-in memory sticks that we call PDA!What goes around definitely comes back. :)Everything old is new again!

RE: Faking Multidimensional Arrays in T-SQL made easy!

Joshua M Perry — Tue, 04 Nov 2008 13:34:01 GMT

This is great. Going back to using the concept of a stack and heap. This is way faster than using a cursor to pivot data. Now if you could just find a way to run SQL Server on a MOS 6502...

RE: Faking Multidimensional Arrays in T-SQL made easy!

stax68 — Tue, 04 Nov 2008 13:30:41 GMT

[quote][b]Jeff Moden (11/4/2008)[/b][hr]Keep it clean, slick. ;) [/quote]Oh, I do - mens sana in corpore sano old chap.[quote] And, don't blame me if you don't understand what I'm saying. :w00t:[/quote]in that case may I congratulate you on your magnificent imperial robe!

RE: Faking Multidimensional Arrays in T-SQL made easy!

Randal Burke — Tue, 04 Nov 2008 13:23:58 GMT

WoW! This seems to have brought out a ton of thinkers!The article is to provide a look back into history as to how arrays were first created - be they 2d or 3d or more.There are going to be some follow-on articles about how to use this in practical, everyday (for some of us) applications, and possibly even to go on into rocket science.A couple of comments, here might be appropriate though -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.2) Captain Kirk once said, in his final battle with Khan - "He is evidencing classic two dimensional thinking" - we all evidence that quite often. 3) Just because it is new and improved does not mean it is necessarily better.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.This is the concept in the real engineering world - not the concepts that are taken in the lofty world of education.Randal BurkeI wish there was a "head scratchin' " emoticon here...

RE: Faking Multidimensional Arrays in T-SQL made easy!

Jeff Moden — Tue, 04 Nov 2008 13:13:18 GMT

Keep it clean, slick. ;) And, don't blame me if you don't understand what I'm saying. :w00t:

RE: Faking Multidimensional Arrays in T-SQL made easy!

stax68 — Tue, 04 Nov 2008 13:11:07 GMT

[quote][b]Jeff Moden (11/4/2008)[/b][hr]No, I don't think the article was about 3D arrays... go back and read what I posted in my first post. ;).[/quote]I read it the first time. You seemed to say (and therefore like it or not did say) it was about 3D arrays, but the author didn't go far enough in his explanation.[quote]But, with a little imagination, you can create a "3d" array... or at least a 2 dimensional representation of one.[/quote]NS, Sherlock? [wink]

RE: Faking Multidimensional Arrays in T-SQL made easy!

seng-317651 — Tue, 04 Nov 2008 12:59:33 GMT

why not just do the following?DECLARE @AR TABLE ( ROW int NULL, COL int NULL, VALUE varchar(2000) NULL )-- add some dummy dataINSERT @AR (ROW, COL, VALUE) SELECT 5,7,'DATA IN ELEMENT 5,7'-- retrieve itSELECT VALUE FROM @AR WHERE ROW = 5 AND COL = 7it's much easier...seng

RE: Faking Multidimensional Arrays in T-SQL made easy!

Jeff Moden — Tue, 04 Nov 2008 12:54:23 GMT

RE: Faking Multidimensional Arrays in T-SQL made easy!

Jeff Moden — Tue, 04 Nov 2008 12:52:53 GMT

[quote][b]stax68 (11/4/2008)[/b][hr][quote][b]Jeff Moden (11/4/2008)[/b][hr]Nah... not true... in SQL Server 2005 and up, there's basically no limit to passing arrays...[url]http://www.sqlservercentral.com/articles/T-SQL/63003/[/url]The author just didn't go far enough in showing how to use the EAV tables he made in his examples as a 3 dimensional array. Most people blow a couple of gears for anything above a 3 dimensional array so 3 dimensions is a good place to stop. People that need more than that will be able to figure it out quite easily.Randal, nice job at "standing" up an array in "memory". It's a simple concept that far to many folks have forgotten and some have never learned. It's good to see it in print.[/quote]Hello Jeff M? Any explanation of the above forthcoming?[/quote]Heh... you gotta be more patient... I actually work for a living, ya know? :P

RE: Faking Multidimensional Arrays in T-SQL made easy!

Jeff Moden — Tue, 04 Nov 2008 12:51:51 GMT

[quote][b]stax68 (11/4/2008)[/b][hr]Jeff M:What isn't true? I am talking about the limitations on passing table variables, not string representations of arrays, so the link provided seems beside the point. Can you explain your cryptic remarks about 'standing up arrays in memory? Why do you think that the original post was about 3D arrays? I am utterly baffled by your post, and am sure many others must be too. Please explain.[/quote]No, I don't think the article was about 3D arrays... go back and read what I posted in my first post. ;)But, with a little imagination, you can create a "3d" array... or at least a 2 dimensional representation of one.

RE: Faking Multidimensional Arrays in T-SQL made easy!

stax68 — Tue, 04 Nov 2008 10:52:50 GMT

Unfortunately you have wasted your time because the code is utterly worthless. Sorry, but there is no other way to describe it.

RE: Faking Multidimensional Arrays in T-SQL made easy!

mmcclure — Tue, 04 Nov 2008 10:40:30 GMT

Okay, corrected the code for SQL 2008 (at least this is what worked for me)./* create the basic table structures needed */CREATE TABLE [X_DIMENSION] ( [LOC] [int] NULL , [X_ELEMENT] [varchar] (2000) NULL )CREATE TABLE [Y_DIMENSION] ( [LOC] [int] NULL, [Y_ELEMENT] [varchar] (2000) NULL )CREATE TABLE [X_Y_INDEX] ( X_LOC [int] NULL, Y_LOC [int] NULL )/* Now we create some data to place into the tables, indexed */INSERT X_Y_INDEX (X_LOC, Y_LOC)SELECT 5,7INSERT X_DIMENSION (LOC,X_ELEMENT)SELECT 5,'DATA IN ELEMENT 5 'INSERT Y_DIMENSION (LOC,Y_ELEMENT)SELECT 7,'REMAINING DATA FOR ELEMENT 5,7'DECLARE @X INT, @Y INTSET @X = 5 -- or whatever method of loading you wantSET @Y = 7SELECT A.X_ELEMENT + B.Y_ELEMENT FROM X_DIMENSION A, Y_DIMENSION B WHERE A.LOC = @X AND B.LOC = @Y/* The Query returns the concatenated value! *//* DATA IN ELEMENT 5, REMAINING DATA IN ELEMENT 7*/

RE: Faking Multidimensional Arrays in T-SQL made easy!

JRoughgarden — Tue, 04 Nov 2008 10:12:53 GMT

Very poor. Exhibits a fundamental lack of understanding of multi-dimensional arrays. This shows why academic journals have articles reviewed before they're published.

RE: Faking Multidimensional Arrays in T-SQL made easy!

dave1982 — Tue, 04 Nov 2008 08:36:48 GMT

Use SQL server for what it is good at. Storing, retreiving and updating sets of data. Trying to do other operations is why people have to keep upgrading their SQL server boxes!

RE: Faking Multidimensional Arrays in T-SQL made easy!

stax68 — Tue, 04 Nov 2008 08:31:15 GMT

[quote][b]Jeff Moden (11/4/2008)[/b][hr]Nah... not true... in SQL Server 2005 and up, there's basically no limit to passing arrays...[url]http://www.sqlservercentral.com/articles/T-SQL/63003/[/url]The author just didn't go far enough in showing how to use the EAV tables he made in his examples as a 3 dimensional array. Most people blow a couple of gears for anything above a 3 dimensional array so 3 dimensions is a good place to stop. People that need more than that will be able to figure it out quite easily.Randal, nice job at "standing" up an array in "memory". It's a simple concept that far to many folks have forgotten and some have never learned. It's good to see it in print.[/quote]Hello Jeff M? Any explanation of the above forthcoming?

RE: Faking Multidimensional Arrays in T-SQL made easy!

voutmaster — Tue, 04 Nov 2008 08:25:42 GMT

There is nothing interesting presented in this article. Database tables are there to provide functionality that goes way beyond a simple array (even a multi-dimensional one). Obviously, you can simulate array behavior using database tables.

RE: Faking Multidimensional Arrays in T-SQL made easy!

stax68 — Tue, 04 Nov 2008 07:05:08 GMT

Jeff M:What isn't true? I am talking about the limitations on passing table variables, not string representations of arrays, so the link provided seems beside the point. Can you explain your cryptic remarks about 'standing up arrays in memory? Why do you think that the original post was about 3D arrays? I am utterly baffled by your post, and am sure many others must be too. Please explain.

RE: Faking Multidimensional Arrays in T-SQL made easy!

jay-h — Tue, 04 Nov 2008 07:03:08 GMT

I can hardly imagine requiring multidimensional arrays in anything other than procedural code. I suppose this is a potential example of why we might need the CLR languages which handle this type of problem very easily.When your tool has to be stretched to do the job, perhaps it's the wrong tool.

RE: Faking Multidimensional Arrays in T-SQL made easy!

Jeff Moden — Tue, 04 Nov 2008 05:22:38 GMT

Nah... not true... in SQL Server 2005 and up, there's basically no limit to passing arrays...[url]http://www.sqlservercentral.com/articles/T-SQL/63003/[/url]The author just didn't go far enough in showing how to use the EAV tables he made in his examples as a 3 dimensional array. Most people blow a couple of gears for anything above a 3 dimensional array so 3 dimensions is a good place to stop. People that need more than that will be able to figure it out quite easily.Randal, nice job at "standing" up an array in "memory". It's a simple concept that far to many folks have forgotten and some have never learned. It's good to see it in print.

RE: Faking Multidimensional Arrays in T-SQL made easy!

stax68 — Tue, 04 Nov 2008 05:10:18 GMT

Agree with IanS re the first example. The second one is fine for storing values but is trivial - everyone knows that you can store data in a table or table variable, and using a pair of index values as a composite key is an obvious thng to do under any circumstances to which this solution is relevant.The delimited (or fixed-width member) string approaches and my preferred XML (not a string!) solution are used not because SQL programmers didn't think of using a table (!!!), but because using permanent or temp tables is a clunky way of implementing arrays and there are extreme limitations on using table variables to pass arrays between code entities in TSQL, and between TSQL and other code structures with which the database needs to communicate.Sorry to be so critical but this is not a useful article.

RE: Faking Multidimensional Arrays in T-SQL made easy!

ians — Tue, 04 Nov 2008 01:54:23 GMT

The first example is just wrong. It has a table for one item per row, a table for one item for each column, and a seemingly pointless x_y index table. And the column names in the inserts dont match the table definitions, it cant possibly have been tested.Very poor.

RE: Faking Multidimensional Arrays in T-SQL made easy!

Jackx — Tue, 04 Nov 2008 01:45:29 GMT

Thanks for the facinating post. My mind had wondered onto this problem before but immediatly thought it unsurmountable in so far as a simple, tidy solution goes. Genius.However, I can only see the second example working. The first example wouldn't be able to store enough values. e.g. in a simple 10 x 10 matrix there would be 100 values but your example would only store 20? I'm I missing something? AppreciatedJack, from the Jersey in the UK

Faking Multidimensional Arrays in T-SQL made easy!

Randal Burke — Mon, 03 Nov 2008 22:46:19 GMT

Comments posted to this topic are about the item [B]Faking Multidimensional Arrays in T-SQL made easy![/B]