SQL Server Cursors Interview Preparation Guide

Download PDF

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.

Cursor requires a network roundtrip each time it fetches a record, thus consume network resources.
While data processing, it issues locks on part of the table, or on the whole table.

Disadvantages of cursors

Uses more resources because Each time you fetch a row from the cursor, it results in a network roundtrip
There are restrictions on the SELECT statements that can be used.
Because of the round trips, performance and speed is slow

The main purpose that cursors fulfill is traversing of the row set. Cursors can be best avoided by:

a. Using the SQL while loop: Using a while loop we can insert the result set into the temporary table.

b. User defined functions : Cursors are sometimes used to perform some calculation on the resultant row set. This cam also be achieved by creating a user defined function to suit the needs

Three types of locks

READ ONLY: This prevents any updates on the table.
SCROLL LOCK: This allows you to make changes to the table.
OPTIMISTIC: This checks if any change in the table record since the row fetched before updating.
If there is no change, the cursor can update

Temporary table

This can improve processing speed but consume disk space.

Table variable

Table variable that can be used in stored procedures, functions and batches.
Table variable get destroyed at the end of the stored procedure, function or batch in which it is defined.
Since it can be used in stored procedure, it is compiled once and can be used many times.
You can't create index on the Table variable.
Since you can create index on the temporary table, it is good where data is huge.

You can use keyword SCROLL to make cursor Scrollable.
It can scroll to any row and can access the same row in the result set multiple times.
A non-scrollable cursor is also known as forward-only and each row can be fetched at most once.

Cursors can support various functionalities that are listed here.

Cursor allow to position at specific rows of the result set.
Cursor can retrieve one or more rows in the result set.
Cursor allows modification to the rows in the result set.

There are 2 ways to control Cursor behavior:

Cursor Types: Data access behavior depends on the type of cursor; forward only, static, keyset-drive and dynamic.
Cursor behaviors: Keywords such as SCROLL and INSENSITIVE along with the Cursor declaration define scrollability and sensitivity of the cursor.