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 a row by row. Given its importance on SQL and Stored procedures, Cursor is also very popular in SQL interviews. One of the popular SQL questions on Cursor is close vs deallocate. Since both of them sounds to close the cursor, once the job is done, What is the 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 of a cursor frees up all the resources associated with the cursor, including the 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 gets closed automatically.

Similarly terminating database connection from Server, also closes and deallocates any open cursors.

Btw, if you are new to the SQL world, it's better to start with a comprehensive SQL course like these SQL and Database courses for beginners. That will help you to learn SQL better and quicker, and these kinds of articles will also make more sense once you have some SQL knowledge under your belt.

Cursor - Close vs Deallocate in SQL

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

1) Deallocating a cursor automatically closes it, the reverse is not true.

2) Closing an open cursor, only releases the 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, the 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, remain 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. By the way, cursor should be avoided if possible as they often provide poor performance. 

Other related SQL queries, Interview questions, and articles:

• 12 Database Index Questions from interviews (index questions)
• How to find the second highest salary in a table? (solution)
• 5 Courses to learn Database and SQL Better (courses)
• Difference between View and Materialized View in Database? (answer)
• Top 5 Websites to learn SQL online for FREE? (resource)
• Write a SQL query to copy or backup a table in MySQL (solution)
• 5 Courses to learn Oracle and Microsoft SQL Server database (courses)
• How to migrate SQL queries from Oracle to SQL Server 2008? (answer)
• What is the difference between UNION and UNION ALL in SQL? (answer)
• Top 5 Books to learn Advanced SQL and Database Design (books)
• Difference between clustered and non-clustered indexes in SQL? (answer)
• 5 Free Courses to learn T-SQL and SQL Server for Beginners (Courses)
• 5 Best PostgreSQL Courses for Beginners (online courses)
• Difference between char  and varchar in SQL (char vs varchar)
• 10 Free SQL and Database Courses for Beginners (free courses)
• Difference between the Unique and Primary keys in the table? (answer)
• Top 5 Courses to learn MySQL Database for Beginners (Courses)

Thanks for reading this article so far. If you like these SQL Interview question answers then please share them with your friends and colleagues. If you have any questions or feedback then please drop a note.

P.S. - If you are new to SQL and interested in learning Microsoft SQL Server in depth and looking for best resources to start your journey then you can also checkout these Microsoft SQL Server online courses to learn T-SQL and SQL Server in depth.