What are cursors? Explain different types of cursors. What are the disadvantages
of cursors? How can you avoid cursors?
Cursors allow row-by-row processing of the result sets.
Types of cursors: Static, Dynamic, Forward-only, Keyset-driven. See books online for more
Disadvantages of cursors: Each time you fetch a row from the cursor, it results in a
network roundtrip, where as a normal SELECT query makes only one rowundtrip, however
large the resultset is. Cursors are also costly because they require more resources and
temporary storage (results in more IO operations). Furthere, there are restrictions on the
SELECT statements that can be used with some types of cursors.
Most of the times, set based operations can be used instead of cursors. Here is an example:
If you have to give a flat hike to your employees using the following criteria:
Salary between 30000 and 40000 — 5000 hike
Salary between 40000 and 55000 — 7000 hike
Salary between 55000 and 65000 — 9000 hike
In this situation many developers tend to use a cursor, determine each employee’s salary
and update his salary according to the above formula. But the same can be achieved by
multiple update statements or can be combined in a single UPDATE statement as shown
UPDATE tbl_emp SET salary =
CASE WHEN salary BETWEEN 30000 AND 40000 THEN salary + 5000
WHEN salary BETWEEN 40000 AND 55000 THEN salary + 7000
WHEN salary BETWEEN 55000 AND 65000 THEN salary + 10000
Another situation in which developers tend to use cursors: You need to call a stored
procedure when a column in a particular row meets certain condition. You don’t have to
use cursors for this. This can be achieved using WHILE loop, as long as there is a unique key
to identify each row.