Cursor in SQL server

A SQL cursor is a database object that is used to retrieve data from a result set one row at a time. A SQL cursor is used when the data needs to be updated row by row. This article explains everything about SQL cursors. In this article, we will learn the following:

1. Introduction to SQL cursor
2. Cursor life cycle
3. Why and when use a cursor
4. How to implement cursors
5. What are the limitation of SQL cursor
6. How can we replace a SQL Cursor 

SQL Cursor Life Cycle

The following steps are involced in a SQL cursor life cycle. 

1. Declaring Cursor
   A cursor is declared by defining the SQL statement.
    
2. Opening Cursor
   A cursor is opened for storing data retrieved from the result set.
    
3. Fetching Cursor
   When a cursor is opened, rows can be fetched from the cursor one by one or in a block to do data manipulation.
    
4. Closing Cursor
   The cursor should be closed explicitly after data manipulation.
    
5. Deallocating Cursor
   Cursors should be deallocated to delete cursor definition and release all the system resources associated with the cursor.

Why use a SQL Cursor?

In relational databases, operations are made on a set of rows. For example, a SELECT statement returns a set of rows which is called a result set. Sometimes the application logic needs to work with one row at a time rather than the entire result set at once. This can be done using cursors.

In programming, we use a loop like FOR or WHILE to iterate through one item at a time, the cursor follows the same approach and might be preferred because it follows the same logic.

SQL Cursor Syntax

DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ]
[ FORWARD_ONLY | SCROLL ]
 [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
[ TYPE_WARNING ] FOR select_statement
 [ FOR UPDATE [ OF column_name [ ,...n ] ] ] [;]

SQL

Cursor Example

The following cursor is defined for retrieving employee_id and  employee_name from Employee table. The FETCH_STATUS value is 0 until there are rows. When all rows are fetched then  FETCH_STATUS becomes 1.

use Product_Database
SET NOCOUNT ON;

DECLARE @emp_id int ,@emp_name varchar(20),
    @message varchar(max);

PRINT '-------- EMPLOYEE DETAILS --------';

DECLARE emp_cursor CURSOR FOR
SELECT emp_id,emp_name
FROM Employee
order by emp_id;

OPEN emp_cursor

FETCH NEXT FROM emp_cursor
INTO @emp_id,@emp_name

print 'Employee_ID  Employee_Name'

WHILE @@FETCH_STATUS = 0
BEGIN
    print '   ' + CAST(@emp_id as varchar(10)) +'           '+
        cast(@emp_name as varchar(20))


    FETCH NEXT FROM emp_cursor
INTO @emp_id,@emp_name

END
CLOSE emp_cursor;
DEALLOCATE emp_cursor;

SQL

The output of the above program will be as follows

What are the limitations of a SQL Cursor

A cursor is a memory resident set of pointers -- meaning it occupies memory from your system that may be available for other processes.

Cursors can be faster than a while loop but they do have more overhead.

Another factor affecting cursor speed is the number of rows and columns brought into the cursor. Time how long it takes to open your cursor and fetch statements.

Too many columns being dragged around in memory, which are never referenced in the subsequent cursor operations, can slow things down.

The cursors are slower because they update tables row by row. 

How can we replace SQL Cursors 

There's one replacement for cursors in SQL server joins.

Suppose we have to retrieve data from two tables simultaneously by comparing primary keys and foreign keys. In these types of problems, the cursor gives very poor performance as it processes through each and every column. On the other hand using joins in those conditions is feasible because it processes only those columns which meet the condition. So here joins are faster than cursors.

The following example explains the replacement of cursors through joins.

Suppose, we have two tables, ProductTable and Brand Table. The primary key of BrandTable is brand_id which is stored in ProductTable as foreign key brand_id. Now suppose, I have to retrieve brand_name from BrandTable using foreign key brand_id from ProductTable. In these situations, cursor programs will be as follows,

use Product_Database
SET NOCOUNT ON;

DECLARE @brand_id int
DECLARE @brand_name varchar(20)


PRINT '--------Brand Details --------';

DECLARE brand_cursor CURSOR FOR
SELECT distinct(brand_id)
FROM ProductTable;

OPEN brand_cursor

FETCH NEXT FROM brand_cursor
INTO @brand_id

WHILE @@FETCH_STATUS = 0
BEGIN
    select brand_id,brand_name from BrandTable where brand_id=@brand_id
--(@brand_id is of ProductTable)

    FETCH NEXT FROM brand_cursor
INTO @brand_id

END
CLOSE brand_cursor;
DEALLOCATE brand_cursor;

SQL

The output of the above program will be as follows

The same program can be done using joins as follows,

Select distinct b.brand_id,b.brand_name from BrandTable b inner join
ProductTable p on b.brand_id=p.brand_id

SQL

The output of the above program will be as follows

As we can see from the above example, using joins reduces the lines of code and gives faster performance in case huge records need to be processed.