I have been watching the articles and discussions regarding T-SQL and arrays for a while now, but have never seen a method that did not involve convoluted loops and substrings or other method of trying to put all of the supposed array data into a long string variable - even in XML!

I have been around in computers since the 1970's and have seen a lot of things regarding array handling. In the earliest computers ( APPLE II, COMMODORE 64, ATARI, original IBM PC,etc.) there was little in the way of array handling, but it was a simple thing for the interpreter or compiler - depending on the machine - to handle.

So, It occurred to me that since SQL is so good at keeping things straight using indexes, why not go all the way back to the beginnings of array manipulation (like was done in MBASIC, CBASIC and other early compiler and assembler languages?

It was common to maintain three areas of memory in earlier compilers for handling arrays - for a two dimensional array one did a DECLARE statement to fix the boundaries of the array and the compiler set aside a single segment of memory to hold the pointers to the two dimensions. An example

DIMENSION A (2000,2000)

This would set aside an area of memory labeled "A" which was segmented into two INTEGER WIDTH numeric values side by side in memory. Contained in this area of memory was an OFFSET VALUE into the DATA AREA of the compiled program space, where the array element data was stored. Granted that the array data was then stored sequentially through memory, there was still the concept of RANDOMNESS to retrieval of the stored data.

We can do exactly the same thing in T-SQL!

Follow the code and you will see how easy it can be -

/* 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_DIMENSION)
SELECT 5,'DATA IN ELEMENT 5 '
INSERT Y_DIMENSION
 (LOC,Y_DIMENSION)
SELECT 7,'REMAINING DATA FOR ELEMENT 5,7'
/* now that we have some data present, we can call it up by referencing the contents of the X_Y_INDEX table just by setting a variable to the elements we want to retrieve! */DECLARE @X INT, @Y INT
SET @X = 5 -- or whatever method of loading you want
SET @Y = 7
SELECT A.X_DIMENSION + B.Y_DIMENSION
 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
*/

So, try loading multiple values into various elements of X and Y and you can always get it back by providing the X-Y Coordinates!

Another method, which is just as easy, is to create a data table with TWO indexes:

CREATE TABLE [XY_DIMENSIONAL_DATA]
 (
 [LOC_X] [int] NULL,
 [LOC_Y] [int] NULL,
 [DATA_ELEMENT [varchar] (2000) NULL
 )
/* Again, we load data using any method we want, as long as we keep the X-Y Coordinates handled */INSERT XY_DIMENSIONAL_DATA
 (X_LOC, Y_LOC, DATA_ELEMENT)
SELECT 5,7,'DATA IN ELEMENT 5,7 )
DECLARE @X INT, @Y INT
SET @X = 5 -- or whatever method of loading you want
SET @Y = 7
SELECT DATA_ELEMENT
 FROM XY_DIMENSIONAL_DATA
 WHERE LOC_X = @X AND LOC_Y = @Y
/* AND OF COURSE, THE RETURNED VALUE IS *//*
DATA IN ELEMENT 5,7
*/

There is nothing wrong with using TABLE data types either, and would of course increase speed in some cases, but placing the array data into actual tables means that you can come back to the data at any time and update it, reload it or manipulate it without having to fully reload the array should the data be of a long term value, as well.

There is also the concept of retrieving a segment of the data stored in a table element using the indexes and substrings, but then you are dealing with having to do calculations and manipulations. This method really bypasses all of the string manipulations and other excessive coding and just does the job, easily and quickly.

So, you see - there REALLY IS a way to emulate the functions of an array in T-SQL which neither violates any undocumented rules or methods, and can easily be set up as a callable stored procedure that can be used in any T-SQL script.