Faking Multidimensional Arrays in T-SQL made easy!

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Randal Burke » Faking Multidimensional Arrays in T-SQL made...

39 posts, Page 2 of 4

««1 2 3 4 »»»

Faking Multidimensional Arrays in T-SQL made easy!

Rate Topic

Display Mode

Topic Options

Author

Message

JRoughgarden

Posted Tuesday, November 04, 2008 10:12 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 29, 2013 6:07 PM
Points: 36, Visits: 167

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

mmcclure

Posted Tuesday, November 04, 2008 10:40 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, March 28, 2013 3:03 PM
Points: 3, Visits: 40

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

stax68

Posted Tuesday, November 04, 2008 10:52 AM

Say Hey Kid

Group: General Forum Members
Last Login: Tuesday, December 22, 2009 8:22 AM
Points: 696, Visits: 42

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

Jeff Moden

Posted Tuesday, November 04, 2008 12:51 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:57 PM
Points: 32,906, Visits: 26,790

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

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #596835

Jeff Moden

Posted Tuesday, November 04, 2008 12:52 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:57 PM
Points: 32,906, Visits: 26,790

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

Post #596836

Jeff Moden

Posted Tuesday, November 04, 2008 12:54 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:57 PM
Points: 32,906, Visits: 26,790

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

Post #596837

seng-317651

Posted Tuesday, November 04, 2008 12:59 PM

Forum 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

stax68

Posted Tuesday, November 04, 2008 1:11 PM

Say Hey Kid

Group: General Forum Members
Last Login: Tuesday, December 22, 2009 8:22 AM
Points: 696, Visits: 42

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

Jeff Moden

Posted Tuesday, November 04, 2008 1:13 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:57 PM
Points: 32,906, Visits: 26,790

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

--Jeff Moden

Post #596865

Randal Burke

Posted Tuesday, November 04, 2008 1:23 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, June 01, 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 »

39 posts, Page 2 of 4

««1 2 3 4 »»»

Permissions