Post
Ask Question

Cursor In SQL Server

Introduction

 
This article is all about cursors in SQL Server.
 

Cursors

  • Cursor is used to update the values row by row.
  • Fetch the record by record.
  • Cursor is actually always on the database server side.
  • The cursor usually takes up temporary memory space of some sort inside the database.
  • A cursor is needed in some specialized cases, such as when you must execute a stored procedure for row in a record set. However, it is usually possible to rewrite to used a set-based technique (e.g. re-implement stored procedure to join the result set). A set-based technique is nearly always the Preferred method.
In order to work with a cursor we need to perform some steps in the following order
  1. Declare cursor
  2. Open cursor
  3. Fetch row from the cursor
  4. Process fetched row
  5. Close cursor
  6. Deallocate cursor

Types of Cursors

  • Base table
  • Static
  • Fast - Forward-only
  • Keyset-driven
  • Read only
Base Table Cursors
  • Base table cursors are the lowest level of cursors.
  • These cursors work directly against the storage engine and are the fastest of all supported cursor types.
  • Base table cursors can scroll forward or backward with minimal cost, and can be updated.
Static Cursors
  • A static cursor, referred to as a scrollable query cursor.
  • It creates and stores a complete copy of the result set.
Forward-only Cursors
  • The forward-only cursor is the fastest cursor that you can update, but it does not support scrolling.
  • It supports 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.
Keyset-driven Cursors
  • The keyset-driven cursor is a scrollable cursor that you can update.
  • A keyset-driven cursor is controlled by a set of physical identifiers known as the keyset.
  • The keyset is based on all the rows that qualified for the SELECT statement at the time the cursor was opened.
  • The keyset is built in a temporary table when the cursor is opened.
FETCH command also having various types,
  1. NEXT
  2. PRIOR
  3. FIRST
  4. LAST
  5. ABSOLUTE
  6. RELATIVE
E.g.: - For Forward-only Cursors
  1. DECLARE @Complaint_Id Int  
  2. DECLARE Merge_Cursor CURSOR FAST_FORWARD FOR  
  3. Select Cust_CMP_Id from CR_Complaint_Master  
  4.   
  5. Open Merge_Cursor  
  6. FETCH NEXT FROM Merge_Cursor INTO @Complaint_Id  
  7. WHILE @@FETCH_STATUS = 0  
  8. BEGIN  
  9.   
  10. UPDATE CR_Complaint_Master  
  11.         SET  
  12.             Cust_CMP_State= 'Karnataka'              
  13.         WHERE  
  14.             Cust_CMP_Id = @Complaint_Id  
  15.    
  16.   FETCH NEXT FROM Merge_Cursor INTO @Complaint_Id  
  17. END  
  18.   
  19. CLOSE Merge_Cursor  
  20. DEALLOCATE Merge_Cursor  
Advantages of Cursor
  • Used to update the values row by row.
Disadvantages of Cursor
  • Mostly avoid the cursors. Because it reduce the performance and scalability of your applications.
Here are some alternatives to using a cursor,
  • Use WHILE LOOPS
  • Use temp tables
  • Use derived tables
  • Use correlated sub-queries
  • Use the CASE statement
  • Perform multiple queries 


Similar Articles