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
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