Types of Cursors in SQL Server

Hello friends, we have already talked about Cursors in SQL Server and how cursor works. Today we will talk about types of cursors supported by SQL Server 2008. There are four types of cursors available in SQL server 2008 which are following.
  • Static Cursor
  • Dynamic Cursor
  • Forward Only Cursor
  • Keyset Cursor

Static Cursor:- Its name depicts the nature of the cursor, the result set hold by the cursor is static throughout the life cycle of the cursor. Any change (Insert, Update, Delete) done by the cursor or by any other user does not reflect in the result set hold by the cursor. Static cursor is scrollable in nature and can move in forward and backward direction. Lets take an example to understand these facts.

To declare a Static cursor we just write the keyword “Static” after “Cursor” keyword as shown in the below statement.

Declare Cur_emp  cursor Static for select Eid, Name from Emp

First of all we will create a table “Emp” using below SQL statement and insert few rows into it.

CREATE TABLE [dbo].[Emp](
      [Eid] [int] NULL,
      [Name] [varchar](15) NULL
) ON [PRIMARY]

Insert into Emp values(115,'Mohan Sharma')
Insert into Emp values(116,'Priyanka Gandhi')
Insert into Emp values(107,'Akash Rajput')
Insert into Emp values(108,'Vidushi Pandey')
Insert into Emp values(109,'Shivika Garg')
Insert into Emp values(110,'Pranav Kumar')

Lets run below SQL script in which we have defined a static cursor and see the results.

declare @Eid int
declare @Name varchar(50)
Declare Cur_emp  cursor static for select Eid, Name from Emp
open Cur_emp
fetch next from Cur_emp into @Eid,@Name
while @@FETCH_STATUS =0
begin
      update Emp set Name='Vikas Sharma' where Eid =110
      print cast(@Eid as varchar(10)) + ' ' + @Name
      fetch next from Cur_emp into @Eid,@Name
end
close Cur_emp
deallocate Cur_emp


We have updated the employee name where eid=110, which is the last record in the "Emp" table. But you can see in the above screenshot, old name is reflecting in the message window(encircled by red color).Since Static cursors are scrollable hence they support the (Prior, First and Last) movements.
Below are the examples of usage of these movements.

fetch prior from Cur_emp into @Eid,@Name à fetch the previous record from the cursor result set

fetch first from Cur_emp into @Eid,@Name à fetch the first record from the cursor result set

fetch last from Cur_emp into @Eid,@Name à fetch the last record from the cursor result set

Dynamic Cursor:- Dynamic cursor holds a dynamic result set which reflects all the changes(Insert, Update, Delete)by the cursor or by the other users. Dynamic cursor is also scrollable, it’s the default nature of the dynamic cursor to be scrollable. Lets take an example and see how it works.

To declare a dynamic cursor we just write the keyword “dynamic” after “Cursor” keyword as shown in the below statement.

Declare Cur_emp  cursor dynamic for select Eid, Name from Emp

In the below example, I am using the same employee table but I have truncated the table first and then inserted the data which was originally there in the first example.


In the above screenshot, we have declared the dynamic cursor and updating the name of the employee where eid=110 and in the result window we can see that changes are getting reflected.

Forward Only Cursor:- Its name depicts the nature of the cursor, It only supports the forward scrolling that means Prior, Last, first do not work in forward Only Cursor. It is also dynamic in nature which means the changes done by the cursor and other users reflects in the cursor result set.

To declare a dynamic cursor we just write the keyword “Forward_Only” after “Cursor” keyword as shown in the below statement.

Declare Cur_emp  cursor Forward_Only for select Eid, Name from Emp 

declare @Eid int
declare @Name varchar(50)
Declare Cur_emp  cursor forward_only for select Eid, Name from Emp
open Cur_emp
fetch next from Cur_emp into @Eid,@Name
while @@FETCH_STATUS =0
begin
      update Emp set Name='Vikas Sharma' where Eid =110
      print cast(@Eid as varchar(10)) + ' ' + @Name
      fetch next from Cur_emp into @Eid,@Name
end
close Cur_emp
deallocate Cur_emp


In the above screenshot we can see that changes are getting reflected in forward only cursor.
  
We cannot use below statements in forward only cursor.

fetch prior from Cur_emp into @Eid,@Name
fetch first from Cur_emp into @Eid,@Name
fetch last from Cur_emp into @Eid,@Name

Keyset Cursor:- Keyset cursor uses the set of keys that uniquely identify the cursor's rows (keyset), so that the membership and order of rows in the cursor are fixed when the cursor is opened. SQL Server uses a table in tempdb to store keyset. The KEYSET cursor allows updates non key values from being made through this cursor, but inserts made by other users are not visible. Updates non key values made by other users are visible as the owner scrolls around the cursor, but updates key values made by other users are not visible. If a row is deleted, an attempt to fetch the row returns an @@FETCH_STATUS of -2.

To declare a dynamic cursor we just write the keyword “Keyset” after “Cursor” keyword as shown in the below statement.

Declare Cur_emp  cursor Keyset for select Eid, Name from Emp

Note:- Using cursors is an expensive task, try to avoid using cursors as much as possible. We should use while loop or correlated sub queries instead of using cursors.

I hope after reading this article you will be able to understand how different type of cursors work.
Please let me know, if have any query about this article.

No comments: