SQL Server Cursors Interview Preparation Guide

Declare the cursor,
Open the cursor,
Fetch record row by row,
Close cursor,
Deallocate cursor.

Example of a cursor

Declare @EmpId int
Declare curEmp CURSOR READ_ONLY FOR SELECT EmpId FROM Employee
Open curEmp
Fetch next from curEmp into @EmpId
While @@FETCH_STATUS = 0
Begin
Print @EmpId
Fetch next from curEmp into @EmpId
End
Close curEmp
Deallocate curEmp

A Cursor is a database object that represents a result set and is used to manipulate data row by row.
When a cursor is opened, it is positioned on a row and that row is available for processing.
SQL Server supports three types of cursor namely Transact-SQL server cursor, API server cursor, and client cursor.
Transact-SQL Server cursors use Transact-SQL statements and are declared using DECLARE CURSOR statement.
Transact-SQL Server cursors can be used in Transact-SQL scripts, stored procedures, and triggers.
Transact-SQL cursors are implemented on the server.
You can fetch only one row at a time in Transact-SQL Server cursors.
You can use FETCH statements with Transact-SQL cursors to retrieve rows from a cursor’s result set.
API server cursors support the API cursor functions.
API server cursors are implemented on the server.
API server cursors support fetching blocks of rows with each fetch.
A cursor fetches multiple rows at a time is called a block cursor

Close cursor when it is not required.
You shouldn’t forget to deallocate cursor after closing it.
You should fetch least number of records.
You should use FORWARD ONLY option when there is no need to update rows.

DYNAMIC: It reflects changes happened on the table while scrolling through the row.
STATIC: It works on snapshot of record set and disconnects from the server. This kind doesn’t reflects changes happened on the table while scrolling through the row.
KEYSET: In this kind, new record is not reflected, but data modification can be seen

Implicit cursors: these cursors are invoked implicitly. User need not create, open, fetch or close the cursor.

Key set driven : It is a scrollable cursor that cannot be updated. These cursors are controlled by a set of physical identifiers called as key set. The keyset is built in a temporary table when the cursor is opened.

Forward – only cursors / Read only cursor: These are the fastest of the cursors and cannot be updated. They cannot be created on query that returns only read only columns.

Forward – only cursors: This cursor supports updates but not scrolling. It supports only fetching serially. Rows are not retrieved from the database until they are fetched.

Static Cursor: Stores a complete copy of the result set. Used mostly where scrolling is required. Static cursors don’t support updates.

Explicit cursors: these cursors are not invoked implicitly. User needs to create, open, fetch or close the cursor.