Difference between close and deallocate cursor in SQL

Cursor in a database is used to retrieve data from the result set, mostly one row at a time. You can use Cursor to update records and perform an operation on a row by row. Given its importance on SQL and Stored procedure, Cursor is also very popular on SQL interviews. One of the popular SQL question on Cursor is close vs deallocate. Since both of them sounds to close the cursor, once the job  is done, What is a real difference between close and deallocate of Cursor in SQL? Well, there is some subtle difference e.g. closing a cursor doesn't change its definition. In Sybase particular, you can reopen a closed cursor and when you reopen it, it creates a new cursor based upon the same SELECT query. On the other hand, deallocation a cursor frees up all the resources associated with the cursor, including cursor name. You just cannot reuse a cursor name by closing it, you need to deallocate it. By the way, if you deallocate an open cursor, it's get closed automatically. Similarly terminating database connection from Server, also closes and deallocates any open cursors.

Cursor - Close vs Deallocate in SQL

Apart from main difference between close and deallocate Cursor, here are couple of more points to remember them :

1) Deallocating a cursor automatically closes it, the reverse is not true.
2) Closing an open cursor, only releases current result set and free up any cursor locks held on rows, on which the cursor is positioned while deallocate completely removes cursor reference.

3) You can reopen a close cursor and can perform the fetch operation on that, this is not possible once you deallocate a cursor. For example, following is legal :

close employee_cursor
open  employee_cursor

You can then fetch from employee_cursor, as a normally opened cursor. In Sybase Adaptive Server, any conditions associated with a cursor, remains in effect, 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 last cursor reference is deallocated, the data structures comprising the cursor are released.

That's all on the difference between closing or deallocating a Cursor in SQL. It's SQL best practice to not only close but also deallocate any opened cursor so that all the associated resource can be released and reused.