Cursors in SQL Server

Cursors
-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.
-A cursor is used to access the result set stored in the memory on execution of a query.


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


Different types of cursors

1. DYNAMIC: It reflects changes happened on the table while scrolling through the row.

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

3.KEYSET: In this kind, new record is not reflected, but data modification can be seen.
Define the cursor lock types.

SQL Server supports three types of cursor namely Transact-SQL server cursor, API server cursor, and client cursor.


Disadvantages/limitation of the cursor
- consume network resources.
- Close cursor when it is not required.
- You shouldn’t forget to deallocate cursor after closing it.
- Cursor requires a network roundtrip each time it fetches a record.

Avoid cursors
- Using the SQL while loop
- User defined functions
- Using CTE(Common Table Expression) Using WITH Clause.
-Temporary table
- Table variable
- You should fetch least number of records.
- You should use FORWARD ONLY option when there is no need to update rows.