Avoid cursors by using TABLE variables

You will find many ways to improve the performance of your T-SQL code if you make TABLE variables your friend.

Many SQL Server professionals regard cursors as the bane of database programming. SQL Server, like most relational

database management systems, is optimized to manipulate data in sets. Cursors operate on data in a row-by-row manner, making a round trip from client to server for each row fetched. The result is often slow performance and excess network traffic.

However, there are some situations where using a cursor is seemingly unavoidable; in particular, operations that need to perform granular row-by-row processing on a table's data. Such operations are all but impossible to perform using standard set-oriented SQL statements. What's needed is a kind of in-memory "holding tank" to hold the results of a SELECT statement, which can then be used as a basis for performing row-at-a-time operations on a table -- without incurring the overhead caused by cursors.

Enter the TABLE datatype, new to SQL Server 2000. Unlike traditional datatypes, a variable assigned to the TABLE datatype (as its name implies) can represent an entire table worth of data. In fact, it can be queried, updated, joined to, and deleted just like a standard table. You can also define IDENTITY and other specialized column types for a TABLE variable (although you cannot create indexes).

Though this new datatype was created primarily for use with table-valued user-defined functions, you can actually use it in any script or stored procedure where you need a temporary storage area to process rows of data. Although you could do basically the same thing with a temp table, remember that a temp table is an actual object that must be physically created on the disk and deleted after use. This in itself creates additional overhead in the form of physical I/O. A TABLE variable, which resides in RAM and is automatically deallocated when the batch completes, can operate at an order of magnitude faster than a temp table.

A TABLE variable is declared in much the same way as any other variable, with the exception that you must define its columns (just as you would with a standard table). The syntax is as follows:

DECLARE @mytable TABLE 
( 
column1 INT IDENTITY, 
column2 CHAR(1) NOT NULL DEFAULT ('') 
) 

Take the following script, for example. It uses a cursor to cycle row-by-row through the sysobjects table and grant permissions on user tables to the PUBLIC role:

DECLARE tblcursor CURSOR FOR 
    SELECT name FROM sysobjects 
    WHERE type='U' 

OPEN tblcursor 
DECLARE @tablename varchar(30) 
FETCH NEXT FROM tblcursor INTO @tablename 

WHILE (@@FETCH_STATUS=0 ) 
    BEGIN 
    EXEC ('GRANT UPDATE ON ' + @tablename 
           + ' TO public') 

    FETCH NEXT FROM tblcursor INTO @tablename 
    END 

CLOSE tblcursor 
DEALLOCATE tblcursor 

Can we improve this script by using a TABLE variable instead of a cursor? Yes. First, we will get rid of our cursor, and instead will declare a TABLE variable to hold the user table names:

DECLARE @tablenames TABLE 
( 
table_id INT IDENTITY, 
table_name SYSNAME 
) 

Why do we need an IDENTITY column? You'll see later. Now let's populate our TABLE variable:

INSERT INTO @tablenames (table_name) 
SELECT name FROM sysobjects 
WHERE type='U' 

Now that we have a holding area for the names of the tables we want to assign permissions to, we're ready to start processing. You will notice that we declared our TABLE variable with an IDENTITY column. Since an IDENTITY column stores an auto-incrementing integer value, this provides a convenient way for us to iterate through rows without needing a cursor; we simply declare a variable to represent the table_id field, and we increment its value by one within the WHILE loop:

DECLARE @i INT 
DECLARE @tablename SYSNAME 

SET @i = 1 

WHILE (@i <= (SELECT MAX(table_id) FROM @tablenames)) 

    BEGIN 
    SELECT @tablename = table_name 
    FROM @tablenames 
    WHERE table_id = @i 

    EXEC ('GRANT UPDATE ON ' + @tablename 
           + ' TO public') 

    SET @i = @i + 1 
    END 

You've now seen how to optimize your queries by using TABLE variables to replace cursors. Although you may not be able to do this in every single situation, you will delifitely find many ways to improve the performance of your T-SQL code if you make TABLE variables your friend.

For More Information

  • What do you think about this tip? E-mail us at editor@searchDatabase.com with your feedback.
  • The Best SQL Server Web Links: tips, tutorials, scripts, and more.
  • Have an SQL Server tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
  • Ask your technical SQL Server questions--or help out your peers by answering them--in our live discussion forums.
  • Check out our Ask the Experts feature: Our SQL, Database Design, Oracle, SQL Server, and DB2 gurus are waiting to answer your toughest questions.

This was first published in February 2005

Dig deeper on SQL Server Database Modeling and Design

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close