Storing arrays as large character strings is not a new concept. In fact, in the 1980s, the Advanced Revelation DBMS garnered quite a following through its support of “multivalued” columns—essentially string fields with multiple values and special routines to manipulate them. Even today, many DBMSs that support array columns store them internally as simple buffers and provide SQL extensions that insulate the developer from having to know or deal with this. Here’s a sample query that demonstrates the multivalued column approach in Transact-SQL:

This technique stores multiple values in the work table’s arraycol column. These values emulate a single-dimensional array, which is the easiest type to work with using this approach. Multidimensional arrays are feasible as well, but they’re exponentially more complex to deal with. Rather than building multidimensional arrays into a single row, another way to accomplish the same thing is to spread the dimensions of the array over the entire table, with each record representing just one row in that array.

Note the use of varchar(8000) to define the array column. With the advent of SQL Server’s large character data types, we can now store a reasonably sized array using this approach. In the case of an array whose elements are fifteen bytes long, we can store up to 533 items in each array column, in each row. That’s plenty for most applications.

Also note that virtually any type of data can be stored in this type of virtual array, not just strings. Of course, anything stored in a character column must be converted to a string first, but that’s a minor concern. The only prerequisite is that each item must be uniformly sized, regardless of its original data type.

The INSERT statements used to populate the table are intentionally split over multiple lines to mimic filling an array. Though it’s unnecessary, you should consider doing this as well if you decide to use this approach. It’s more readable and also helps with keeping each element sized appropriately—an essential for the technique to work correctly.

Note the use of the expression (n*s)+1 to calculate each array element’s index. Here, n represents the element number (assuming a base of zero) you wish to access, and s represents the element size. Though it would be easier to code

using the expression establishes the relationship between the element you seek and the string stored in the varchar column. It makes accessing any element as trivial as supplying its array index.

This technique does not require that the number of elements be uniform between rows in the table. Here’s an example that shows how to implement “jagged” or unevenly sized arrays:

The only thing that’s really different here is the data. Since SUBSTRING() returns an empty string when passed an invalid starting point, we don’t need special handling for arrays with fewer than six elements.

The example above is limited to arrays with six or fewer elements. What if we want to support arrays of sixty elements? What if we need arrays with hundreds of elements? Are we forced to include a separate column in the result set for each one? The technique would be rather limited if we had to set up a separate result set column for every element. That would get cumbersome in a hurry. Here’s some code that demonstrates how to handle arrays of any size without coding static result set columns for each element:

This code opens a cursor on the work table, then iterates through the array in each row. It uses the DATALENGTH() function to determine the length of each array and a loop to SELECT each element from the array using the indexing expression introduced in the previous query.

Though this technique is flexible in that it allows us to process as many array elements as we want with a minimum of code, it suffers from one fundamental flaw: It returns multiple result sets. Many front ends don’t know how to handle multiple result sets and will balk at query output such as this. There are a couple of ways around this. Here’s one approach:

Here, we use a temporary table to store each array element as it’s processed by the query. Once processing completes, the contents of the table are returned as a single result set and the temporary table is dropped. A variation on this would be to move the code to a stored procedure and return a pointer to the cursor via an output parameter. Then the caller could process the array at its convenience.

Another, though slightly more limited, way to process the array is to generate a SELECT statement as the array is processed and execute it afterward. Here’s an example:

(Results abridged)

Note the use of the QUOTENAME() function to surround each array value with quotes so that it can be returned by the SELECT statement. The default quote delimiters are '[' and ']' but, as the example shows, you can specify others.

This routine is more limited than the temporary table solution because it’s restricted by the maximum size of varchar. That is, since the SELECT statement that we build is stored in a variable of type varchar, it can’t exceed 8000 bytes. While the other techniques allot 8000 bytes for each row’s array, this one limits the sum length of the arrays in all records to just 8000 bytes. Given that this string must also store a column name for each element (most front ends have trouble processing unnamed columns), this is a significant limitation.

Nevertheless, the fact that this approach builds SQL that it then executes is interesting in and of itself. You can probably think of other applications for this technique such as variably sized cross-tabs, run and sequence flattening, and so on.

Implementing a virtual array using a simple table is also a viable alternative to native array support. This technique uses one or more columns as array indexes. If the array is single-dimensional, there’s just one index column. If it’s multidimensional, there may be several. Here’s an example:

This code illustrates a simple way to emulate a single-dimensional array using a table. Note the use of a seed value for the identity column in order to construct a zero-based array, as we did in the string array examples. Transact-SQL requires that you also specify an increment value whenever you specify a seed value, so we specified an increment of one.

This code changes the value of the third element (which has an index value of two). Removing the WHERE clause would allow the entire virtual array to be set or cleared.