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:
Post a Comment