Page 120 - CITS - Computer Software Application -TT

COMPUTER SOFTWARE APPLICATION - CITS

35 --Fetch the next record into the variables.
36 FETCH NEXT FROM PrintCustomers INTO
37 @id, @c_name, @city
38 END
39

40 --Close the cursor
41 CLOSE PrintCustomers
42
43 --Deallocate the cursor
44 DEALLOCATE PrintCustomers

After executing a cursor, we will get the below output:
Limitations of SQL Server Cursor
A cursor has some limitations so that it should always use only when there is no option except the cursor. These
limitations are:
• Cursor consumes network resources by requiring a network roundtrip each time it fetches a record.
• A cursor is a memory resident set of pointers, which means it takes some memory that other processes could
use on our machine.

• It imposes locks on a portion of the table or the entire table when processing data.

• The cursor’s performance and speed are slower because they update table records one row at a time.
• Cursors are quicker than while loops, but they do have more overhead.
• The number of rows and columns brought into the cursor is another aspect that affects cursor speed. It refers
to how much time it takes to open your cursor and execute a fetch statement.
How can we avoid cursors?
The main job of cursors is to traverse the table row by row. The easiest way to avoid cursors are given below:
Using the SQL while loop
The easiest way to avoid the use of a cursor is by using a while loop that allows the inserting of a result set into
the temporary table.
User-defined functions
Sometimes cursors are used to calculate the resultant row set. We can accomplish this by using a user-defined
function that meets the requirements.

107

CITS : IT&ITES - Computer software application - Lesson 18 - 36