Monday, May 22, 2023

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.