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 Monday, November 3, 2008 10:46 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 1, 2009 7:27 AM
Points: 3, Visits: 26
Comments posted to this topic are about the item Faking Multidimensional Arrays in T-SQL made easy!
Post #596321
Posted Tuesday, November 4, 2008 1:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 2:21 AM
Points: 1, Visits: 56
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?

Appreciated

Jack, from the Jersey in the UK
Post #596378
Posted Tuesday, November 4, 2008 1:54 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, July 22, 2010 9:06 AM
Points: 632, Visits: 79
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.




Post #596379
Posted Tuesday, November 4, 2008 5:10 AM
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: Friday, July 11, 2014 1:42 PM
Points: 696, Visits: 43
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.




Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
Post #596472
Posted Tuesday, November 4, 2008 5:22 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 8:12 AM
Points: 35,770, Visits: 32,434
Nah... not true... in SQL Server 2005 and up, there's basically no limit to passing arrays...

http://www.sqlservercentral.com/articles/T-SQL/63003/

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.


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

(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 #596479
Posted Tuesday, November 4, 2008 7:03 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 12:11 PM
Points: 771, Visits: 1,971
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.


...

-- FORTRAN manual for Xerox Computers --
Post #596540
Posted Tuesday, November 4, 2008 7:05 AM
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: Friday, July 11, 2014 1:42 PM
Points: 696, Visits: 43
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.




Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
Post #596542
Posted Tuesday, November 4, 2008 8:25 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, September 15, 2012 1:55 PM
Points: 31, Visits: 34
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.
Post #596621
Posted Tuesday, November 4, 2008 8:31 AM
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: Friday, July 11, 2014 1:42 PM
Points: 696, Visits: 43
Jeff Moden (11/4/2008)
Nah... not true... in SQL Server 2005 and up, there's basically no limit to passing arrays...

http://www.sqlservercentral.com/articles/T-SQL/63003/

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.


Hello Jeff M?
Any explanation of the above forthcoming?




Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
Post #596627
Posted Tuesday, November 4, 2008 8:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 4:39 AM
Points: 7, Visits: 105
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!
Post #596630
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse