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
JRoughgarden
JRoughgarden
SSC-Enthusiastic
SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)

Group: General Forum Members
Points: 131 Visits: 195
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
mmcclure
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 47
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
stax68
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1423 Visits: 44
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
Jeff Moden
SSC Guru
SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)

Group: General Forum Members
Points: 115539 Visits: 41423
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. Wink
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.
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)

Group: General Forum Members
Points: 115539 Visits: 41423
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? Tongue

--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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)

Group: General Forum Members
Points: 115539 Visits: 41423
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.


Agreed... you can't pass tables. Just strings that can quickly be shredded into arrays. Wink

--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
seng-317651
seng-317651
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 24
why not just do the following?

DECLARE @AR TABLE
(
ROW int NULL,
COL int NULL,
VALUE varchar(2000) NULL
)

-- add some dummy data
INSERT @AR (ROW, COL, VALUE) SELECT 5,7,'DATA IN ELEMENT 5,7'

-- retrieve it
SELECT VALUE FROM @AR WHERE ROW = 5 AND COL = 7

it's much easier...
seng
stax68
stax68
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1423 Visits: 44
Jeff Moden (11/4/2008)

No, I don't think the article was about 3D arrays... go back and read what I posted in my first post. Wink.


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.


But, with a little imagination, you can create a "3d" array... or at least a 2 dimensional representation of one.


NS, Sherlock? [wink]

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)

Group: General Forum Members
Points: 115539 Visits: 41423
Keep it clean, slick. Wink And, don't blame me if you don't understand what I'm saying. w00t

--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
Randal Burke
Randal Burke
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 26
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 Burke

I wish there was a "head scratchin' " emoticon here...
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