Distinction between shut and deallocate cursor in SQL

Cursor in a database is used to retrieve knowledge from the end result set, principally one row at a time. You need to use Cursor to replace information and carry out an operation a row by row. Given its significance on SQL and Saved procedures, Cursor can be very fashionable in SQL interviews. One of many fashionable SQL questions on Cursor is shut vs deallocate. Since each of them sounds to shut the cursor, as soon as the job is finished, What’s the actual distinction between shut and deallocate of Cursor in SQL? Properly, there may be some delicate distinction e.g. closing a cursor does not change its definition. In Sybase specific, you possibly can reopen a closed cursor and while you reopen it, it creates a brand new cursor based mostly upon the identical SELECT question.

Alternatively, deallocation of a cursor frees up all of the assets related to the cursor, together with the cursor identify. You simply can not reuse a cursor identify by closing it, you could deallocate it. By the way in which, when you deallocate an open cursor, it is will get closed mechanically.

Equally terminating database connection from Server, additionally closes and deallocates any open cursors.

Btw, in case you are new to the SQL world, it is higher to begin with a complete SQL course like these SQL and Database programs for inexperienced persons. That can make it easier to to study SQL higher and faster, and these sorts of articles may also make extra sense upon getting some SQL information underneath your belt.

Cursor – Shut vs Deallocate in SQL

Other than the principle distinction between shut and deallocate Cursor, listed here are a few extra factors to recollect:

1) Deallocating a cursor mechanically closes it, the reverse just isn’t true.

2) Closing an open cursor, solely releases the present end result set and liberate any cursor locks held on rows, on which the cursor is positioned whereas deallocate utterly removes cursor reference.

3) You possibly can reopen an in depth cursor and may carry out the fetch operation on that, this isn’t doable when you deallocate a cursor. For instance, the next is authorized :

shut employee_cursor
open  employee_cursor

You possibly can then fetch from employee_cursor, as a usually opened cursor. In Sybase Adaptive Server, any situations related to a cursor, stay in impact, even after reopening a closed cursor.

fetch employee_crsr
emp_id       emp_name           age
----------- ------------------- ---------------
16032243    John               31
16032243    Johnny             32
16032243    Robin              33

deallocate employee_cursor

4) In Microsoft SQL Server, when the final cursor reference is deallocated, the info buildings comprising the cursor are launched.

That is all on the distinction between closing or deallocating a Cursor in SQL. It is SQL greatest apply to not solely shut but in addition deallocate any opened cursor so that every one the related useful resource may be launched and reused. By the way in which, cursor needs to be prevented if doable as they usually present poor efficiency.