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 4, 2008 10:12 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 18, 2014 2:06 PM
Points: 37, Visits: 176
Very poor. Exhibits a fundamental lack of understanding of multi-dimensional arrays. This shows why academic journals have articles reviewed before they're published.


Post #596721
Posted Tuesday, November 4, 2008 10:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 15, 2014 12:06 PM
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*/
Post #596750
Posted Tuesday, November 4, 2008 10:52 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
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
Post #596755
Posted Tuesday, November 4, 2008 12:51 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: 2 days ago @ 1:53 PM
Points: 35,366, Visits: 31,905
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."

(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 #596835
Posted Tuesday, November 4, 2008 12:52 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: 2 days ago @ 1:53 PM
Points: 35,366, Visits: 31,905
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? :P


--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 #596836
Posted Tuesday, November 4, 2008 12:54 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: 2 days ago @ 1:53 PM
Points: 35,366, Visits: 31,905
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. ;)


--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 #596837
Posted Tuesday, November 4, 2008 12:59 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 24, 2012 6:33 PM
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
Post #596845
Posted Tuesday, November 4, 2008 1:11 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: Friday, July 11, 2014 1:42 PM
Points: 696, Visits: 43
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. ;).


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
Post #596862
Posted Tuesday, November 4, 2008 1:13 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: 2 days ago @ 1:53 PM
Points: 35,366, Visits: 31,905
Keep it clean, slick. ;) And, don't blame me if you don't understand what I'm saying.

--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 #596865
Posted Tuesday, November 4, 2008 1:23 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
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...
Post #596871
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse