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.
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.
Further Learning
Introduction to SQL
The Complete SQL Bootcamp
SQL for Newbs: Data Analysis for Beginners
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.
Further Learning
Introduction to SQL
The Complete SQL Bootcamp
SQL for Newbs: Data Analysis for Beginners
No comments :
Post a Comment