processing ; cursor vs (temp) table syntax

Introduction

We will often find people still using cursors, as this seem(ed) to be the only way to process 1 record at a time.
However, while cursors are (still) valid for processing, especially for larger data sets with concurrent data it's NOT the best way.
The main reasons are:

Note that for really highly concurrent scenarios, even the table processing method will eventually not be the best, but this article is limited to show the syntax differences between the two options.

cursor code example

First, let's start with a simple cursor example, which you can copy/paste to your SQL server box:

-- starting with the variables you will need in the processing part
                      declare @id int
                      declare @name sysname
                      declare @l nvarchar(max)
                      
                      -- here comes the part for the cursor definition, which includes the SQL you want to run actually
                      -- if your process requires some "order" in the processing, put the corresponding ORDER BY in this SQL
                      declare c cursor  for 
                         select id, name from sysobjects;
                      open c;
                      
                      --- get the first record, you need to specify a variable for each field in the cursor's SQL
                      fetch next from c into @id, @name;
                      
                      -- continue to execute as long as the previous fetch succeeds (first one above or the fetch in the loop).
                      WHILE @@FETCH_STATUS = 0
                      BEGIN
                      
                         -- process the data, this is the part you can modify as needed to your real needs
                         set @l =  cast(@id as sysname) + ' ' + @name
                         print @l
                      
                         -- now, get the next record
                         fetch next from c into @id, @name;
                      END
                      -- cleaning up the cursor resources
                      close c;
                      deallocate c; 

Select all Open in new window

The code is straightforward, nothing really tricky or complex, as by the book

non-cursor code version

Here I wrote the same processing without a cursor, and you can see that the code is almost identical, and has the same low complexity level as the cursor code. There is only 1 constraint to this process: it requires a primary/unique key field. In my example, the id field returned is known to be unique, so I simply used that. If your query does not have such a field, you can define the table with a identity field and use that one instead.
Note: Table Variables are available as from MS SQL Server 2005, for versions prior to that, you would need to create a temporary or real table instead.

-- starting with the variables you will need in the processing part
                      declare @id int
                      declare @name sysname
                      declare @l nvarchar(max)
                      
                      -- here is the table variable definition, which lives only for the duration of the run and is cleaned up automatically
                      -- for "small" results, it will stay purely in memory; larger sets may be "stored" in the temp database
                      declare @c table ( id int , name sysname )
                      -- fill the table with data, update your select as you need it
                      -- if your process requires some "order" in the processing, still an ORDER BY here will be useless (unless you also use and need a TOP X clause) see below on where you have to put the ORDER BY
                      
                      set nocount on
                      insert into @c (id, name) select id, name from sysobjects;
                      
                      -- process, either if the above insert did at least 1 row, or if the below "delete" did indeed 
                      while @@ROWCOUNT <> 0
                      begin
                        -- fetch 1 record from the table. 
                        -- if your process requires some "order" in the processing, put the corresponding ORDER BY here
                        select top 1 @id = id, @name = name from @c
                      
                        if @@ROWCOUNT <> 0
                        begin
                         -- process the data here, exactly the same as with the cursor example.
                         set @l =  cast(@id as sysname) + ' ' + @name
                         print @l
                        end
                      
                        -- here we delete the row from the temp table , which is nothing else than a processing queue table, hence
                        delete @c where id = @id
                      END 

Select all Open in new window

notes & wrap-up

in my day-to-day processing, I often put only the primary key field of what I have to process later into this processing table, and the query inside the loop used that key field to fetch the necessary data. This makes the initial query as fast as possible, and the processing will fetch data still base on a primary key, so will still be very efficient. In all cases, the final solution depends on your concrete scenario.

as conclusion, old-school cursor users shall start changing their code to use less overhead resources, impact less on the locks, and still have simple code.

I hope this quick article helps you writing your processing code.