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.