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

DECLARE @Complaint_Id Int
DECLARE Merge_Cursor CURSOR FAST_FORWARD FOR
Select Cust_CMP_Id from CR_Complaint_Master

Open Merge_Cursor
FETCH NEXT FROM Merge_Cursor INTO @Complaint_Id
WHILE @@FETCH_STATUS = 0
BEGIN

UPDATE CR_Complaint_Master
        SET
            Cust_CMP_State= 'Karnataka'           
        WHERE
            Cust_CMP_Id = @Complaint_Id
 
  FETCH NEXT FROM Merge_Cursor INTO @Complaint_Id
END

CLOSE Merge_Cursor
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