Database Cursor

A cursor is a database object that enables traversal over the records in a database on a row by row basis. This article describes the basic overview of SQL cursor. Summary of the article:

  • What is SQL Cursor?
  • Cursor Format or Example of SQL Cursor
  • Output of a Cursor
  • Cursor Optimization Tips

What is SQL Cursor?
Cursor is a database control structure to travels a data set or table row by row. Its as like as loop statement used in different programming language.
We need to do the following to use cursors:

  • Declare a cursor
  • Open the cursor
  • Fetch the data into local variables row by row
  • Close the cursor after finished

Cursor Format or Example of SQL Cursor
This is a simplest example of SQL cursor. We can use it to write any SQL cursor.

DECLARE @Temp TABLE ( 
      CustomerNo        INT,
      CustomerName      NVARCHAR(100), 
      InvoiceNo         INT 
      ) 

INSERT into @Temp VALUES(1,'Mr. Smith',100001) 
INSERT into @Temp VALUES(2,'Mr. John',100002) 
INSERT into @Temp VALUES(3,'Mr. Devid',100003) 

DECLARE @Name NVARCHAR(100) 
DECLARE dbCursor CURSOR FOR
SELECT  CustomerName 
FROM  @Temp 
OPEN dbCursor
FETCH NEXT FROM dbCursor INTO @Name
WHILE @@FETCH_STATUS =0   
BEGIN         
       PRINT(@Name)   
       FETCH NEXT FROM dbCursor INTO @Name
END   
CLOSE dbCursor  
DEALLOCATE dbCursor

Output of a Cursor
If we run the above cursor we get the following output:
Mr. Smith
Mr. John
Mr. Devid

Cursor Optimization Tips
SQL cursor is very cost effective.So we need to follow some tips to optimize our cursor.

  • Try to avoid using cursors if it is possible
  • De-allocate cursor when the data processing is completed
  • Try to process less rows & columns in the cursor
  • Try to select fewer columns when you declare a cursor
  • Don’t select unnecessary columns
  • Try to use READ ONLY cursors whenever possible instead of up-datable cursors
  • If possible try to avoid using insensitive, static and key set cursors
  • Use FAST_FORWARD cursors if it is possible

That’s all about SQL cursor.

Comments

  1. Reply

Leave a Reply

Your email address will not be published. Required fields are marked *