<

processing ; cursor vs (temp) table syntax

Published on
20,780 Points
3,980 Views
3 Endorsements
Last Modified:
Awarded

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:
cursors use much more internal resources than the alternative solutions
cursors are impacting more in locks of the data (as the data is locked during the full duration of the process)
cursors are impacted by any locks of the data (same as above)

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; 

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 

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.
3
Comment
0 Comments

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Join & Write a Comment

Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month