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:

  •     Introduction to SQL cursor
  •     Cursor life cycle
  •     Why and when use a cursor
  •     How to implement cursors
  •     What are the limitation of SQL cursor
  •     How can we replace a SQL Cursor

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

    Declaring Cursor
    A cursor is declared by defining the SQL statement.

    Opening Cursor
    A cursor is opened for storing data retrieved from the result set.

    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.

    Closing Cursor
    The cursor should be closed explicitly after data manipulation.

    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 ] ] ] [;] 
 

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;   


The Output of the above program will be as follows

SQL Server

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 [email protected]_id 
    --(@brand_id is of ProductTable) 
         
        FETCH NEXT FROM brand_cursor    
    INTO @brand_id  
      
    END    
    CLOSE brand_cursor;   
    DEALLOCATE brand_cursor;   


The Output of the above program will be as follows

SQL Server

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

The Output of the above program will be as follows

SQL Server

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.