Cursor In SQL Server

Introduction

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.

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 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.

• A static cursor, referred to as a scrollable query cursor.
• It creates and stores a complete copy of the result set.

• 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.

• 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.

1. NEXT
2. PRIOR
3. FIRST
4. LAST
5. ABSOLUTE
6. RELATIVE

• Used to update the values row by row.

• Mostly avoid the cursors. Because it reduce the performance and scalability of your applications.

• Use WHILE LOOPS
• Use temp tables
• Use derived tables
• Use correlated sub-queries
• Use the CASE statement
• Perform multiple queries