Faking Multidimensional Arrays in T-SQL made easy!

Question

Faking Multidimensional Arrays in T-SQL made easy!

Randal Burke

SSC Enthusiast

Points: 155
More actions
November 3, 2008 at 10:46 pm

#195585

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

Viewing 15 posts - 1 through 15 (of 38 total)

You must be logged in to reply to this topic. Login to reply

Jackx SSC Enthusiast Points: 103 More actions · Answer 1

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

ians SSC Eights! Points: 964 More actions · Answer 2

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.

stax68 SSChampion Points: 11711 More actions · Answer 3

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

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 4

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jay-h SSCoach Points: 18816 More actions · Answer 5

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

stax68 SSChampion Points: 11711 More actions · Answer 6

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

voutmaster Mr or Mrs. 500 Points: 517 More actions · Answer 7

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.

stax68 SSChampion Points: 11711 More actions · Answer 8

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

dave1982 SSC Enthusiast Points: 101 More actions · Answer 9

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!

JRoughgarden Ten Centuries Points: 1119 More actions · Answer 10

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

mmcclure Grasshopper Points: 23 More actions · Answer 11

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,7

INSERT 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 INT

SET @X = 5 -- or whatever method of loading you wantSET @Y = 7

SELECT 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*/

stax68 SSChampion Points: 11711 More actions · Answer 12

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

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 13

stax68 (11/4/2008)
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.

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.

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

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 14

stax68 (11/4/2008)
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?

Heh... you gotta be more patient... I actually work for a living, ya know? 😛

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

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)