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

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

Continues…

Pages: 1 2 3




Related Articles :

  • No Related Articles Found

2 Responses to “How to Perform SQL Server Row-by-Row Operations Without Cursors”

  1. The following are the techniques which can be used instead of cursors:

    1. CTE
    2. Loop
    3. Temporary Tables

  2. There’s lots of claims of performance in this article but no substantial evidence that the Temp Table/While Loop replacement for the Cursor is any faster. A well written “firehose” cursor will usually beat a well written Temp Table/While loop combination.

    Yes, cursors are usually a bad thing and should be avoided but only if you can come up with a proper set-based solution. That goes for any form of RBAR including Temp Tables/While loops and recursive CTEs.

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |