Difference Betwixt Unopen Together With Deallocate Cursor Inwards Sql

Cursor inwards a database is used to retrieve information from the final result set, generally 1 row at a time. You tin purpose Cursor to update records as well as perform an performance on a row past times row. Given its importance on SQL as well as Stored procedure, Cursor is likewise really pop on SQL interviews. One of the pop SQL query on Cursor is unopen vs deallocate. Since both of them sounds to unopen the cursor, 1 time the project  is done, What is a existent departure betwixt unopen as well as deallocate of Cursor inwards SQL? Well, at that topographic point is only about subtle departure e.g. closing a cursor doesn't alter its definition. In Sybase particular, yous tin reopen a closed cursor as well as when yous reopen it, it creates a novel cursor based upon the same SELECT query. On the other hand, deallocation a cursor frees upward all the resources associated amongst the cursor, including cursor name. You only cannot reuse a cursor advert past times closing it, yous demand to deallocate it. By the way, if yous deallocate an opened upward cursor, it's larn closed automatically. Similarly terminating database connector from Server, likewise closes as well as deallocates whatever opened upward cursors.

Cursor - Close vs Deallocate inwards SQL

Apart from principal departure betwixt unopen as well as deallocate Cursor, hither are dyad of to a greater extent than points to retrieve them :

1) Deallocating a cursor automatically closes it, the opposite is non true.
2) Closing an opened upward cursor, exclusively releases electrical flow final result fix as well as costless upward whatever cursor locks held on rows, on which the cursor is positioned piece deallocate completely removes cursor reference.

3) You tin reopen a unopen cursor as well as tin perform the fetch performance on that, this is non possible 1 time yous deallocate a cursor. For example, next is legal :

unopen employee_cursor
opened upward  employee_cursor

You tin hence fetch from employee_cursor, every bit a ordinarily opened cursor. In Sybase Adaptive Server, whatever weather condition associated amongst a cursor, remains inwards effect, fifty-fifty later 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 information structures comprising the cursor are released.

That's all on the difference betwixt closing or deallocating a Cursor inwards SQL. It's SQL best practise to non exclusively unopen but likewise deallocate whatever opened cursor hence that all the associated resources tin endure released as well as reused. 

Further Learning
Introduction to SQL
The Complete SQL Bootcamp
SQL for Newbs: Data Analysis for Beginners

Share this post