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:
we declare our table variable first
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
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,
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