USING SQL SERVER 2000 TABLE VARIABLES
TO ELIMINATE THE NEED FOR CURSORS

By Peter A. Bromberg, Ph.D.

Peter Bromberg  

Both cursors and temporary tables place extra demand on the SQL Server database. Programmers who come from one of the older ISAM or VSAM database environments, or from a FOXPRO or DBase environment usually gravitate toward the use of cursors because they seem "familiar".    I know I did!



But let's take a look at what happens in the database when for example, we use a temporary table:


1) We CREATE the temporary table
2) We INSERT data into the newly created table
3) We SELECT data from the temporary table (usually by JOINing to other physical tables) while holding a lock on the entire tempdb database until the transaction has completed.
4) We DROP the temporary table

This involves a lot of disk activity, along with the potential for contention problems because of the locking, and all of this adds up to lousy performance, especially on large or long-running operations. The use of a cursor, as far as database resources are concerned, would look very similar to the above.

However, almost everything that you may first envision as requiring cursors or temporary tables to achieve can actually be done using the new SQL Server TABLE type. This is more or less a "clone" of an actual table, except that it is a variable and it normally operates 100% IN MEMORY, provided there is sufficient RAM (Data Cache) available.. No more contention, no more tempdb locks, no more disk I/O!

  • Records in the TABLE field will be stored in memory, while a TEMP table is included in the TEMPDB database. (if there is not sufficient RAM or the amount of data is too large, even TABLE variables are stored in TEMPDB).
  • Since TABLE variables offer a defined scope and behave as if they are local, a table variable will disappear whenever the function or stored procedure that created that variable is completed, thus saving memory and improving performance.
  • Since TABLE variables don't require that you lock multiple records as a TEMP table does, you get better performance from your database because there are fewer locks to create, manage, and release.
  • A TABLE variable in a stored procedure requires fewer recompilations as data is modified, which improves concurrency and lowers the amount of server resources that must be used.

Let's take a very simple example that you can test for yourself. We'll use the trusty old Northwind sample database. Let's say that you run the Northwind Traders operation and you suddenly find a new shipper whose prices on everything that you normally ship with shipper number "1" that costs over a certain price level ( let's say we've determined the level is $50.25) can now be shipped with your new shipper number 4 for a flat rate of $21. You think, "Oboy - I'll be able to put in for a raise on this one!'

So you want to update all your open orders that had instructions to use Shipper 1 where the price came out to be greater than $50.25, and change the shipper to shipper number 4 and the new flat rate of $21.00.

Now before we begin, I know and you know that we can definitely do this with a single SQL Statement using a correlated subquery with a derived table holding the items to change. But for the sake of simplicity, let's just say for the sake of example that we've decided we need to do it either with a cursor or a temporary table. Now let's see how we could do it using the new TABLE variable, and avoid all the extra disk access and locking:

USE NORTHWIND

-- we declare our table variable first

declare @SpecialCustomers TABLE (
CustomerID nchar (5) NOT NULL ,
OrderID int NOT NULL ,
ShipVia int NOT NULL,
Freight money NOT NULL)

-- now we populate the in-memory table variable with the record information needed for the update

insert into @SPecialCustomers select CustomerID, OrderID, ShipVia, Freight
from dbo.Orders where ShipVia =1 AND Freight >50.25

-- and finally we update the affected records in our regular orders table with our new shipper and price information,
-- using the @SpecialCustomers TABLE variable just as we would a real, physical table in the database:

UPDATE ORDERS SET ShipVia=4, Freight =21.00
where ORDERS.OrderID IN (SELECT ORDERID FROM @SpecialCustomers)

Here's another sample that shows how to iterate through a table variable's records inside of a stored procedure like a recordset without creating a cursor: SQL Server Cursorless Cursor

Easy, fast, simple! It all happens in memory. No locks on the TempDB. Remember, this is not a solution to a real problem, I've shown this simplistic example to illustrate a concept that can help you improve the efficiency and speed of your SQL Server database programming.

 


Peter Bromberg is a C# MVP, MCP, and .NET consultant who has worked in the banking and financial industry for 20 years. He has architected and developed web - based corporate distributed application solutions since 1995, and focuses exclusively on the .NET Platform.