articles >> peformance tuning >> How to Perform SQL Server Row-by-Row Operations ...

How to Perform SQL Server Row-by-Row Operations Without Cursors

By : David VanDeSompele Nov 17, 2004

Printer friendly

SQL cursors have been a curse to database programming for many years because of their poor performance. On the other hand, they are extremely useful because of their flexibility in allowing very detailed data manipulations at the row level. Using cursors against SQL Server tables can often be avoided by employing other methods, such as using derived tables, set-based queries, and temp tables. A discussion of all these methods is beyond the scope of this article, and there are already many well-written articles discussing these techniques.

The focus of this article is directed at using non-cursor-based techniques for situations in which row-by-row operations are the only, or the best method available, to solve a problem. Here, I will demonstrate a few programming methods that provide a majority of the cursor’s flexibility, but without the dramatic performance hit. 

Let’s begin by reviewing a simple cursor procedure that loops through a table. Then we’ll examine a non-cursor procedure that performs the same task.

 

if exists (select * from sysobjects where name = N'prcCursorExample')

   drop procedure prcCursorExample

go

CREATE PROCEDURE  prcCursorExample

AS

/*

**  Cursor method to cycle through the Customer table and get Customer Info for each iRowId.

**

** Revision History:

** ---------------------------------------------------

**  Date       Name       Description      Project

** ---------------------------------------------------

**  08/12/03   DVDS       Create            ----     

**

*/

SET NOCOUNT ON

 

-- declare all variables!

DECLARE  @iRowId  int,

         @vchCustomerName     nvarchar(255),

         @vchCustomerNmbr     nvarchar(10)    

 

-- declare the cursor

DECLARE Customer CURSOR FOR

SELECT    iRowId,

          vchCustomerNmbr,

          vchCustomerName

FROM      CustomerTable

 

OPEN Customer
 

FETCH Customer INTO @iRowId,
                    @vchCustomerNmbr,
                    @vchCustomerName

 

-- start the main processing loop.

WHILE @@Fetch_Status = 0

   BEGIN

   -- This is where you perform your detailed row-by-row

   -- processing.

   -- Get the next row.

   FETCH Customer INTO @iRowId,
                       @vchCustomerNmbr,
                       @vchCustomerName             

   END

CLOSE Customer

DEALLOCATE Customer

RETURN