Cursors

Cursors have terrible performance and should never be used. They are expensive in terms of processing and also lock the entire dataset when in use.

Furthermore, using a temporary table in a cursor is extremely bad because no other process is able to create or drop temporary tables for the duration of the cursor as it prevents them from acquiring the necessary exclusive locks on the tempdb database. The processes are forced to wait and result in poor server performance. Also, referencing a temporary table in a cursor forces the stored procedure to recompile every time.

Most cursors are just used for looping through a dataset and performing other actions. A simple loop can easily be accomplished using the automatic increment functionality (for example, IDENTITY) of a temp table or table variable instead.

For example, instead of using the following cursor:

DECLARE CURSOR MyCursor FOR
SELECT Field1 
FROM DataTable
ORDER BY Field1 ASC

OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @Field1 
WHILE @@FETCH_STATUS = 0
BEGIN
-- process your data
FETCH NEXT FROM MyCursor INTO @Field1 
END

Use a table variable instead in the following manner:
DECLARE @MyTable TABLE (	RowId	int IDENTITY,
                               Field1	int)

DECLARE	@Rows  int,
              @Row	int

-- Insert data here in the order desired 
INSERT INTO @MyTable (Field1) 
SELECT Field1
FROM DataTable 
ORDER BY Field1 ASC

-- Get the total number of rows
SELECT	@Rows  = @@ROWCOUNT,
             @Row	= 0

-- Loop through the rows in the table 
   WHILE @Row < @Rows
      BEGIN
      SELECT @Row = @Row + 1

SELECT @Field1 = Field1 
FROM @MyTable 
WHERE RowId = @Row

-- Process your data 
END