Avoiding Cursors in SQL Server 2005

In this post, I will show you a technique which replicates a cursor in SQL Server. The idea is to avoid the overhead of using cursors in SQL Server.

Problem with using Cursors

Some time ago, I used cursors in one of my stored procedure. This procedure returned data which was consumed by a reporting screen. I was working with a limited amount of data for testing purpose so the screen worked great (at least for a while). But when the same stored procedure ran on the production machine with tables having hundreds of thousands of rows, the screen just went slow like anything. The stored procedure returned the data in almost 10 minutes (only). This was acceptable to me :) but not to my management LOL.

I started to debug the stored procedure and it finally dawned on me that the cursors in the stored procedure were causing the application to choke. After doing some googling binging, it was clear to me that that cursors have a performance overhead.

Cursors in SQL Server are great when we need to process data on a row by row basis. Usually a query returns a static dataset, However; at times, we need to process the data for each row before it is returned. This is where cursors come in handy. However cursors can have a performance issue if not used properly. I will not go into details of describing problems associated with cursors. You can find many useful articles online on this subject.

So the hundred thousand million dollar question is that how we can we avoid cursors but still get the same functionality. The point is to be able to process each row before we can return the data. Well there are many techniques to replicate a cursor. In the following section, I will show you the use of Temporary Table to implement cursor functionality.

Using Temporary Table to avoid Cursors

A Temporary Table is used to store data temporarily. When using a temporary table, the table and its data is stored on the disk in the tempdb database. A temporary table can be local or global. A local temporary table is only visible to user who created it and only within the connection which created it. Local variables get dropped when the connection is closed. The basic syntax of creating a Temporary Table is as follows:

CREATE TABLE #MyTable
(
 ID  int,
 Address varchar (50)
)

On the other hand, a global temporary table is visible to anyone connected to the SQL Server instance. These tables are dropped when the last connection to the table is closed. The syntax for creating temporary table is as follows:

CREATE TABLE ##MyTable
(
 ID  int,
 Address varchar (50)
)

To replicate cursors, I will use temporary table in the following code.

Code Listing

USE Northwind

-- Ref 1 - Create temporary table
CREATE TABLE #EmployeeData 
(
 employeeID  INT UNIQUE CLUSTERED,   
 lastName  VARCHAR (20),  
 firstName  VARCHAR (20),  
 isProcessed  BIT
)

-- Ref 2 - Insert Data in temporary table
INSERT INTO 
 #EmployeeData
 SELECT
  EmployeeID, LastName, FirstName, 0
 FROM
  Employees


DECLARE @employeeID INT
DECLARE @fName  VARCHAR (20)
DECLARE @lName  VARCHAR (20)

-- Ref 3 - Loop through data in the temporary table
WHILE EXISTS (SELECT employeeID FROM #EmployeeData WHERE isProcessed = 0)
BEGIN


 -- Ref 4 - Get top employeeID with flag not set
 SELECT TOP (1) 
  @employeeID = employeeID,
  @lName = lastName,
  @fName = firstName
 FROM 
  #EmployeeData 
 WHERE 
  isProcessed = 0

 -- Now you can process the data. For bravity, I will only print the full-name
 PRINT @fName + ' ' + @lName
 
 -- Ref 4 - Set flag for the currently selected employeeID
 UPDATE #EmployeeData SET isProcessed = 1 WHERE employeeID = @employeeID
END

DROP TABLE #EmployeeData

Let me explain the above reference points one by one:

Ref 1: Create a temporary table. The fourth column will be used as a flag to traverse each row replicating a cursor
Ref 2: Insert table from Employee table into temporary table. Set the flag column equal to 0
Ref 3: Traverse each row in temporary table using the fourth column. Loop through each row until flag is equal to 0
Ref 4: Update the flag to 1 of the currently selected row. This way the control will move to the next row with flag equal to 0

I hope the above code is easy to understand now. One question remains that what if we want to traverse the temporary table again. The answer is that all you have to do is update the table by setting the flag equal to 0 using the following regular Update statement:

Update 
#EmployeeData 
Set 
 isProcessed = 0

Now you are ready to process each row again using the above code.

I hope that this post has been useful. Please feel free to share your thoughts. And yes, stay tuned for more :)…