What is a Cursor?
Database Cursors
A cursor provides row-by-row procedural processing across query results, contrasting with standard set-based operations.
Cursor Lifecycle Steps
| Stage | SQL Command | Action Performed |
|---|---|---|
| 1. Declare | DECLARE cursor_name CURSOR FOR |
Defines the SELECT query |
| 2. Open | OPEN cursor_name |
Executes query and populates dataset |
| 3. Fetch | FETCH NEXT FROM ... INTO |
Loads current row into local variables |
| 4. Close | CLOSE cursor_name |
Releases dataset locks |
| 5. Deallocate | DEALLOCATE cursor_name |
Frees cursor memory completely |
Why to Avoid Cursors
- Performance Bottleneck: Row-by-row iteration incurs extreme overhead compared to relational set-based operations.
- Memory & Locking: Holds locks open for prolonged durations.
- Modern Alternatives: Window functions (
LAG,LEAD,ROW_NUMBER) and CTEs eliminate 99% of cursor use cases.