What is CURSOR? Definition of CURSOR in SQL Server

CURSOR is a server side tool and completely different from ADO.NET’s cursor. It is giving row-by-row solution to the result set and let me tell you that SQL Server impressive with handling set of rows, not row-by-row. This is useful for those who came from procedural background and don’t much familiar with set-based relational algebra.

You can define CURSOR as read only or update but read only is fast as compare with update as read only will gives your data and won’t remember it.

Personally I used to avoid cursor as long as it is possible as it uses lots of resources of server and reduce the performance. Whenever it is possible use temp table, derived table, sub-query, CASE statement but finally you draw conclusion that any of the set-based operation won’t work for your problem than and than go for CURSOR solution but make sure you are not selecting more than necessary rows in CURSOR. Lesser the row, higher the performance!!!!

The main reason for writing this article is to help those who want to understand the concept of CURSOR and another reason CURSOR is not something that you can completely ignore. There may be some situation where you have to use CURSOR, may be some complex logic or dynamic code iteration especially while making code generator.

Type of CURSOR:

Static: This is lowest type of CURSOR and used to use for finding data and generating reports. Once getting the data into the CURSOR you will not identify any modification done in data after retrieving it because it make a copy of your data into the temp table of tempDB.

Forward_Only: This is the default type of CURSOR and really very similar to Static CURSOR the only difference you will find it, it will move forward only. In short, this CURSOR will scroll from first to last no other movement supported.

Fast_Forward: this is a mixture of Forward_Only and Read_Only.

Dynamic: this CURSOR will be bit slow as it will accept any data modification done by any user even after you fetch the data because you scroll around the CURSOR. Data membership, value and its order will be changed in each FETCH if any data modification has been done in record set.

Keyset-Driven: when you open this CURSOR, membership key and order of row are fixed in CURSOR.

Steps for CURSOR:

DECLARE: Defines a CURSOR with standard SELECT statement. This must be done before you open the CURSOR.

OPEN: physically open the CURSOR and received the record set exist in table at the time of opening the CURSOR.

FETCH: CURSOR always points to one row at a time and FETCH is retrieve the value from that row to manipulate it further.

CLOSE: CLOSE will release the lock on table made by CURSOR. If you wish than you can re-open CURSOR after closes it.

DEALLOCATE: Once you are done with CURSOR, do DEALLOCATE it and removes the memory from the server. You can open the CURSOR once you close it but can’t re-open CURSOR once you DEALLOCATE it.

You can run following query for dynamic management function in-order to get information about CURSOR running on your server.

select * from sys.dm_exec_cursors(0)
go
-------------

What is cursor in SQL Server? 

• 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 

What are cursors?

Answer
A cursor is used to access the result set stored in the memory on execution of a query. It is a special programming construct that allows data to be manipulated on a row-by-row basis. They point to a certain location within a record set and allow the operator to move forward (and sometimes backward, depending upon the cursor type) through the results one record at a time.

Define the steps to use Transact-SQL Cursor. 

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

Example of a cursor

DECLARE @EmpID char(11)

DECLARE c1 CURSOR READ_ONLY

FOR

SELECT Employee_ID FROM Employee

OPEN c1

FETCH NEXT FROM c1

INTO @EmpID

PRINT @EmpID

WHILE @@FETCH_STATUS = 0

BEGIN

      PRINT @EmpID

      FETCH NEXT FROM c1

      INTO @EmpID

END

CLOSE c1

DEALLOCATE c1

Explain the cursor types. 

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 

Explain different types of cursors.

Different types of cursors:

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

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

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

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.

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.

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.

Define the cursor lock types. 

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 

Explain in brief the cursor optimization tips. 

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.