Faking Multidimensional Arrays in T-SQL made easy!

  • Randal Burke

    SSC Enthusiast

    Points: 155

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

  • Jackx

    SSC Enthusiast

    Points: 103

    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

    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

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

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • jay-h

    SSCoach

    Points: 18802

    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

    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

    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

    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

    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

    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

    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

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

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Jeff Moden

    SSC Guru

    Points: 994246

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

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

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