A Cursor allow us to retrieve data from a
result set in singleton fashion means row by row. Cursor are required when we
need to update records in a database table one row at a time
A Cursor impacts the performance of the SQL
Server since it uses the SQL Server instances' memory, reduce concurrency,
decrease network bandwidth and lock resources. Hence it is mandatory to
understand the cursor types and its functions so that you can use suitable
cursor according to your needs.
You should avoid the use of cursor. Basically
you should use cursor alternatives like as WHILE loop, sub queries, Temporary
tables and Table variables. We should use cursor in that case when there is no
option except cursor.
Types of Cursors
1.
Static Cursors
A static cursor populates the result set at the time of cursor
creation and query result is cached for the lifetime of the cursor. A static
cursor can move forward and backward direction. A static cursor is slower and
use more memory in comparison to other cursor. Hence you should use it only if
scrolling is required and other types of cursors are not suitable.
You can't update, delete data using static cursor. It is not
sensitive to any changes to the original data source. By default static cursors
are scrollable.
2.
Dynamic Cursors
A dynamic cursor allows you to see the data updation, deletion
and insertion in the data source while the cursor is open. Hence a dynamic
cursor is sensitive to any changes to the data source and supports update,
delete operations. By default dynamic cursors are scrollable.
3.
Forward Only Cursors
A forward only cursor is the fastest cursor among the all
cursors but it doesn't support backward scrolling. You can update, delete data
using Forward Only cursor. It is sensitive to any changes to the original data
source.
There are three more types of Forward Only Cursors.Forward_Only
KEYSET, FORWARD_ONLY STATIC and FAST_FORWARD.
A FORWARD_ONLY STATIC Cursor is populated at the time of creation and cached the data to the
cursor lifetime. It is not sensitive to any changes to the data source.
A FAST_FORWARD Cursor is the fastest cursor and it is not sensitive to any changes to
the data source.
4.
Keyset Driven Cursors
A keyset driven cursor is controlled by a set of unique
identifiers as the keys in the keyset. The keyset depends on all the rows that
qualified the SELECT statement at the time of cursor was opened. A keyset
driven cursor is sensitive to any changes to the data source and supports
update, delete operations. By default keyset driven cursors are scrollable.
SQL SERVER – Examples of Cursors
1.
CREATE TABLE Employee
2.
(
3.
EmpID int PRIMARY KEY,
4.
EmpName varchar (50) NOT NULL,
5.
Salary int NOT NULL,
6.
Address varchar (200) NOT NULL,
7.
)
8.
GO
9.
INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(1,'Mohan',12000,'Noida')
10.INSERT INTO
Employee(EmpID,EmpName,Salary,Address) VALUES(2,'Pavan',25000,'Delhi')
11.INSERT INTO
Employee(EmpID,EmpName,Salary,Address) VALUES(3,'Amit',22000,'Dehradun')
12.INSERT INTO
Employee(EmpID,EmpName,Salary,Address) VALUES(4,'Sonu',22000,'Noida')
13.INSERT INTO
Employee(EmpID,EmpName,Salary,Address) VALUES(5,'Deepak',28000,'Gurgaon')
14.GO
15.SELECT * FROM
Employee
Static Cursor - Example
1.
SET NOCOUNT ON
2.
DECLARE @Id int
3.
DECLARE @name varchar(50)
4.
DECLARE @salary int
5.
DECLARE cur_emp CURSOR
6.
STATIC FOR
7.
SELECT EmpID,EmpName,Salary from Employee
8.
OPEN cur_emp
9.
IF @@CURSOR_ROWS > 0
10. BEGIN
11. FETCH NEXT FROM cur_emp INTO @Id,@name,@salary
12. WHILE @@Fetch_status = 0
13. BEGIN
14. PRINT 'ID : '+ convert(varchar(20),@Id)+', Name
: '+@name+ ', Salary : '+convert(varchar(20),@salary)
15. FETCH NEXT FROM cur_emp INTO @Id,@name,@salary
16. END
17.END
18.CLOSE cur_emp
19.DEALLOCATE
cur_emp
20.SET NOCOUNT OFF
Dynamic Cursor - Example
1.
--Dynamic
Cursor for Update
2.
SET NOCOUNT ON
3.
DECLARE @Id int
4.
DECLARE @name varchar(50)
5.
DECLARE Dynamic_cur_empupdate CURSOR
6.
DYNAMIC
7.
FOR
8.
SELECT EmpID,EmpName from Employee ORDER BY EmpName
9.
OPEN Dynamic_cur_empupdate
10.IF @@CURSOR_ROWS > 0
11. BEGIN
12. FETCH NEXT FROM Dynamic_cur_empupdate INTO
@Id,@name
13. WHILE @@Fetch_status = 0
14. BEGIN
15. IF @name='Mohan'
16. Update Employee SET Salary=15000 WHERE CURRENT OF Dynamic_cur_empupdate
17. FETCH NEXT FROM Dynamic_cur_empupdate INTO
@Id,@name
18. END
19.END
20.CLOSE
Dynamic_cur_empupdate
21.DEALLOCATE
Dynamic_cur_empupdate
22.SET NOCOUNT OFF
23. Go
24.Select * from
Employee
1.
--
Dynamic Cursor for DELETE
2.
SET NOCOUNT ON
3.
DECLARE @Id int
4.
DECLARE @name varchar(50)
5.
DECLARE Dynamic_cur_empdelete CURSOR
6.
DYNAMIC
7.
FOR
8.
SELECT EmpID,EmpName from Employee ORDER BY EmpName
9.
OPEN Dynamic_cur_empdelete
10.IF @@CURSOR_ROWS > 0
11. BEGIN
12. FETCH NEXT FROM Dynamic_cur_empdelete INTO
@Id,@name
13. WHILE @@Fetch_status = 0
14. BEGIN
15. IF @name='Deepak'
16. DELETE Employee WHERE CURRENT OF Dynamic_cur_empdelete
17. FETCH NEXT FROM Dynamic_cur_empdelete INTO
@Id,@name
18. END
19.END
20.CLOSE
Dynamic_cur_empdelete
21.DEALLOCATE
Dynamic_cur_empdelete
22.SET NOCOUNT OFF
23.Go
24.Select * from
Employee
Forward Only Cursor - Example
1.
--Forward
Only Cursor for Update
2.
SET NOCOUNT ON
3.
DECLARE @Id int
4.
DECLARE @name varchar(50)
5.
DECLARE Forward_cur_empupdate CURSOR
6.
FORWARD_ONLY
7.
FOR
8.
SELECT EmpID,EmpName from Employee ORDER BY EmpName
9.
OPEN Forward_cur_empupdate
10.IF @@CURSOR_ROWS > 0
11. BEGIN
12. FETCH NEXT FROM Forward_cur_empupdate INTO
@Id,@name
13. WHILE @@Fetch_status = 0
14. BEGIN
15. IF @name='Amit'
16. Update Employee SET Salary=24000 WHERE CURRENT OF Forward_cur_empupdate
17. FETCH NEXT FROM Forward_cur_empupdate INTO
@Id,@name
18. END
19.END
20.CLOSE
Forward_cur_empupdate
21.DEALLOCATE
Forward_cur_empupdate
22.SET NOCOUNT OFF
23. Go
24.Select * from
Employee
1.
--
Forward Only Cursor for Delete
2.
SET NOCOUNT ON
3.
DECLARE @Id int
4.
DECLARE @name varchar(50)
5.
DECLARE Forward_cur_empdelete CURSOR
6.
FORWARD_ONLY
7.
FOR
8.
SELECT EmpID,EmpName from Employee ORDER BY EmpName
9.
OPEN Forward_cur_empdelete
10.IF @@CURSOR_ROWS > 0
11. BEGIN
12. FETCH NEXT FROM Forward_cur_empdelete INTO
@Id,@name
13. WHILE @@Fetch_status = 0
14. BEGIN
15. IF @name='Sonu'
16. DELETE Employee WHERE CURRENT OF Forward_cur_empdelete
17. FETCH NEXT FROM Forward_cur_empdelete INTO
@Id,@name
18. END
19.END
20.CLOSE
Forward_cur_empdelete
21.DEALLOCATE
Forward_cur_empdelete
22.SET NOCOUNT OFF
23. Go
24.Select * from
Employee
Keyset Driven Cursor - Example
1.
--
Keyset driven Cursor for Update
2.
SET NOCOUNT ON
3.
DECLARE @Id int
4.
DECLARE @name varchar(50)
5.
DECLARE Keyset_cur_empupdate CURSOR
6.
KEYSET
7.
FOR
8.
SELECT EmpID,EmpName from Employee ORDER BY EmpName
9.
OPEN Keyset_cur_empupdate
10.IF @@CURSOR_ROWS > 0
11. BEGIN
12. FETCH NEXT FROM Keyset_cur_empupdate INTO @Id,@name
13. WHILE @@Fetch_status = 0
14. BEGIN
15. IF @name='Pavan'
16. Update Employee SET Salary=27000 WHERE CURRENT OF Keyset_cur_empupdate
17. FETCH NEXT FROM Keyset_cur_empupdate INTO @Id,@name
18. END
19.END
20.CLOSE
Keyset_cur_empupdate
21.DEALLOCATE
Keyset_cur_empupdate
22.SET NOCOUNT OFF
23. Go
24.Select * from
Employee
1.
--
Keyse Driven Cursor for Delete
2.
SET NOCOUNT ON
3.
DECLARE @Id int
4.
DECLARE @name varchar(50)
5.
DECLARE Keyset_cur_empdelete CURSOR
6.
KEYSET
7.
FOR
8.
SELECT EmpID,EmpName from Employee ORDER BY EmpName
9.
OPEN Keyset_cur_empdelete
10.IF @@CURSOR_ROWS > 0
11. BEGIN
12. FETCH NEXT FROM Keyset_cur_empdelete INTO @Id,@name
13. WHILE @@Fetch_status = 0
14. BEGIN
15. IF @name='Amit'
16. DELETE Employee WHERE CURRENT OF Keyset_cur_empdelete
17. FETCH NEXT FROM Keyset_cur_empdelete INTO @Id,@name
18. END
19.END
20.CLOSE
Keyset_cur_empdelete
21.DEALLOCATE
Keyset_cur_empdelete
22.SET NOCOUNT OFF
23. Go Select * from Employee
How to avoid cursors using
cursor's alternatives
The following articles shows how to avoid to use
cursors by it’s alternatives
