Home > Articles > Data > SQL Server

SQL Server Reference Guide

Hosted by

Toggle Open Guide Table of ContentsGuide Contents

Close Table of ContentsGuide Contents

Close Table of Contents

Design Elements Part 14: Arrays

Last updated Mar 28, 2003.

In most programming constructs that somehow map to T-SQL, such as loops, we have to be pretty liberal in our comparisons. T-SQL simply doesn't implement some programming language concepts very well. In others, however, T-SQL actually shines. Using T-SQL, we can write things that aren't available easily in other languages. One such construct is the Array.

An array is a simple enough idea: it's a single variable that acts a bit like a spreadsheet. To understand why you might want to use an array, think of a box of chocolates. When you bring them into the office to share, you bring the entire box – not the individual pieces. However, when you eat the chocolate, there are many different pieces inside. Everyone seems to like different kinds of chocolate. One person picks the cream-filled, another picks the toffee.

That's how an array works. To stretch the analogy a bit, the box is the variable, and the individual chocolates are the "cells" of the array.

To create an array in a high-level programming language, you declare it first, often setting the number of rows and columns it contains. After that, you can fill each cell with values. From there, you refer to the cell by the variable name, followed by the row and column number. Here's a grid of 2 by 2 cells created in Visual Basic, for instance:

Dim grid(2,2) As String

Here, "grid" is the variable name, and it has 2 columns and 2 rows. Here's how you put the value "Toffee" the bottom right cell:

Grid(2,2) = "Toffee"

And you could see the contents of the first cell at the bottom this way:

Print Grid(2,1)

There are other types of arrays, such as ragged, dynamic, and multi-dimensional. You can also create an array that has just one row, and several columns, which is a common way to store things like a range of dates.

There are lots of reasons to use arrays, such as calendars and lists. Just about any grouping of data fits an array.

SQL Server tables are also a kind of array. They are two-dimensional storage places for data, and you can access any discrete cell in them. The difference is that they are persistent, or permanent. You don't normally use them as a variable.

There are times, however, when you need an array variable in your T-SQL code. While T-SQL doesn't have an array variable type, it actually has something a little better: Table Variables.

Table variables are just what they sound like – a variable that holds a table. Here's an example of how to create one:

DECLARE @VariableName TABLE ( Field, FieldType NOT NULL
,...)

The great thing about these variables is that they're handled in memory. Of course, that also means that you've got to be careful with them, since you can bog down your machine if they get too large.

Most often, you'll use table variables to hold little bits of information that you need throughout a stored procedure. They are very handy to hold a set of comparison or update values. Let's take a look at an example:

DECLARE @FirstLastAuthors TABLE 
(
AuthorName varchar (50) NOT NULL
, State char(2) NOT NULL
)

Notice that I declared a variable name, and gave it a TABLE type. Inside the parentheses I followed standard T-SQL syntax for creating a table. There are a few limits here, so it's important to use a table variable for small, discrete data sets. I've posted a few references at the end of this article to explain more about these limits, but basically if you find yourself wanting to put an index on a table variable, the data is probably too large for a table variable.

Table variables are also used to store transformed data to use in another query. Table variables are also used to form the basis for an UPDATE statement as a set of comparison values.

Now that I've created the table variable, I can fill it with the values I want. In this example, I'll transform the au_fname and au_lname fields from the pubs database into a single field in the last-name, first-name format. To work with the table variable, I treat the variable name like a regular table:

INSERT INTO @FirstLastAuthors 
	SELECT au_lname + ', ' + au_fname, state 
	FROM authors
	ORDER BY state

Now that I have values in the table, I can query them to see what they look like.

SELECT * 
FROM @FirstLastAuthors
-----------------------------------

White, Johnson

CA

Green, Marjorie

CA

Carson, Cheryl

CA

O'Leary, Michael

CA

Straight, Dean

CA

Bennet, Abraham

CA

Dull, Ann

CA

Gringlesby, Burt

CA

Locksley, Charlene

CA

Yokomoto, Akiko

CA

Stringer, Dirk

CA

MacFeather, Stearns

CA

Karsen, Livia

CA

Hunter, Sheryl

CA

McBadden, Heather

CA

DeFrance, Michel

IN

Smith, Meander

KS

Panteley, Sylvia

MD

del Castillo, Innes

MI

Blotchet-Halls, Reginald

OR

Greene, Morningstar

TN

Ringer, Anne

UT

Ringer, Albert

UT


Remember that variables in T-SQL are all local – you can't create a variable in one batch and "see" it in another. It is destroyed as soon as you run the batch. That means that all three statements from above have to be run together to work. If you run them separately, you'll get an error message.

In addition to table variables, T-SQL allows you to create another array substitute: the temporary table. I mentioned these kinds of tables briefly in my article on statistics. A temporary table is constructed the same way as any other table in SQL Server, but you preface the table name with a pound sign (#). Once the temporary table is created, it is stored in tempdb until you break the connection (not the batch). When you terminate the connection, the temporary table is destroyed. This type of table is called a local temporary table.

Each stored procedure is a complete connection, so if you're creating the temporary table in a stored procedure, it is destroyed when the stored procedure completes.

Another type of temporary table, called a global temporary table, isn't destroyed when the connection is broken. The global temporary table is retained until the last connection references it. In effect, this is the closest you can get to a global variable in T-SQL. You create a global temporary table by prefacing the table name with two pound signs (##).

Here's the same code we saw earlier in the table variable explanation, this time using a local temporary table:

CREATE TABLE #FirstLastAuthors 
(
AuthorName varchar (50) NOT NULL
, state char(2) NOT NULL
)

INSERT INTO #FirstLastAuthors 
	SELECT au_lname + ', ' + au_fname, state 
	FROM authors
	ORDER BY state

Since I haven't closed my Query Analyzer connection yet, I can query the temporary table later, unlike a table variable.

SELECT * 
FROM #FirstLastAuthors

While temporary tables are very useful, table variables do still have a few advantages. For one thing, they are run in memory, so they are very fast. They aren't written to tempdb, as are temporary tables, so they don't punish the I/O subsystem.

In addition to table variables and temporary tables, you can simulate an array with a User Defined Function (UDF) that returns a table. Here's a sample of that syntax:

USE pubs
GO
CREATE FUNCTION dbo.SimpleTableFunction(@LastName varchar(50))
RETURNS TABLE
AS
RETURN SELECT *
FROM employee
WHERE au_lname LIKE @LastName

The function is permanent, but doesn't store the table data. In a way, it's similar to a view.

The advantage of this type of array is that you can pass the function a variable, as you can see above. Of course, you can have the UDF perform a lot more logic if you need it.

To access the data in this UDF, I'll pass the variable of the last name I'm looking for to the function by using a SELECT statement:

SELECT *
FROM SimpleTableFunction('White')
-----------------------------------

172-32-1176

White

Johnson

408 496-7223

10932 Bigge Rd.

Menlo Park

CA

94025

1


We'll talk more about UDF's in future articles.

To decide when to use each type of array, make an evaluation of the purpose of the array. If you need a quick, in-memory set of values, use a table variable. To use a larger set of data that is visible to more than one connection, use a temporary table. To pass variables to the array, use a User Defined Function.

Online Resources

In this article, Peter Bromberg that describes table variables a little further, and shows how to use them in place of a cursor.

Microsoft explains table variable advantages and limitations in this article on their support site.

InformIT Tutorials and Sample Chapters

Baya Pavliashvili has a great article on the merits of UDFs.