Operations in a relational database act on a complete set of rows. For example, the set of rows returned by a SELECT statement consists of all the rows that satisfy the conditions in the WHERE clause of the statement. This complete set of rows returned by the statement is known as the result set. Applications, especially interactive online applications, cannot always work effectively with the entire result set as a unit. These applications need a mechanism to work with one row or a small block of rows at a time. Cursors are an extension to result sets that provide that mechanism.

Cursors extend result processing by:

Client cursors

Client cursors are implemented internally by the SQL Server Native Client ODBC driver and by the DLL that implements the ADO API. Client cursors are implemented by caching all the result set rows on the client. Each time a client application calls an API cursor function, the SQL Server Native Client ODBC driver or the ADO DLL performs the cursor operation on the result set rows cached on the client.

Type of Cursors

SQL Server supports four cursor types.

1. Forward-only Cursors

A forward-only cursor is specified as FORWARD_ONLY and READ_ONLY and does not support scrolling. These are also called firehose cursors and support only fetching the rows serially from the start to the end of the cursor. The rows are not retrieved from the database until they are fetched. The effects of all INSERT, UPDATE, and DELETE statements made by the current user or committed by other users that affect rows in the result set are visible as the rows are fetched from the cursor.

2. Static Cursors

The complete result set of a static cursor is built in tempdb when the cursor is opened. A static cursor always displays the result set as it was when the cursor was opened. Static cursors detect few or no changes, but consume relatively few resources while scrolling. SQL Server static cursors are always read-only.

3. Keyset Cursors

The membership and order of rows in a keyset-driven cursor are fixed when the cursor is opened. Keyset-driven cursors are controlled by a set of unique identifiers, keys, known as the keyset. The keyset for a keyset-driven cursor is built in tempdb when the cursor is opened.

4. Dynamic Cursors

Dynamic cursors are the opposite of static cursors. Dynamic cursors reflect all changes made to the rows in their result set when scrolling through the cursor. The data values, order, and membership of the rows in the result set can change on each fetch. All UPDATE, INSERT, and DELETE statements made by all users are visible through the cursor.

Examples Of Cursors

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 DB_DNP  
                            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;