How Cursors Works in Sql Server
A Sql cursor is a database object that is used to retrieve data / loop over the bunch of a result set/tuples each row at a time. A SQL cursor is used when we have to manipulate data in each row Or specific row based on some condition(s) and its not feasible by a simple/single TSQL query
SQL Server cursor life cycle
@@FETCHSTATUS function - returns the status of the last cursor FETCH statement executed against the cursor; If @@FETCHSTATUS returns 0, meaning the FETCH statement was successful.
Limitations of a SQL Cursor
- A cursor is a memory resident set of pointers -- meaning it occupies memory from your system that may be available for other processes.
- Cursors can be faster than a while loop but they do have more overhead.
- Another factor affecting cursor speed is the number of rows and columns brought into the cursor. Time how long it takes to open your cursor and fetch statements.
- Too many columns being dragged around in memory, which are never referenced in the subsequent cursor operations, can slow things down.
- The cursors are slower because they update tables row by row.
Comparison with While Loop
- Cursors are a looping construct built inside the database engine. Because of this, there is a little overhead involved in creating the cursor and destroying it. Also, a majority of cursor operations occur in tempdb, so a heavily used tempdb will be even more overloaded with the use of cursors.
- A WHILE loop is a programming construct. No objects is created in memory to facilitate the looping through a set of records which is required in a cursor….