Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Faking Multidimensional Arrays in T-SQL made easy!

By Randal Burke,

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.

Total article views: 10067 | Views in the last 30 days: 7
 
Related Articles
FORUM

Create or Drop Dimension Members

Create or Drop Dimension Members

FORUM

Advice for array oflarge arrays

how would you represent an array of 1020 floats in record

FORUM

Arrays in SQL SERVER

Arrays

ARTICLE

Creating Inferred Dimension Members with SSIS

This article will show you how to use SSIS to create inferred dimension members on the fly during a ...

ARTICLE

Mastering Dimensions of Time

A method to create and populate Date and Time dimension tables for a data warehouse project.

Tags
arrays    
data manipulation    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones