What is a Cursor in SQL?
Learn what a SQL cursor is, its declare-open-fetch-close lifecycle, performance tradeoffs, and when to avoid it.
Expected Interview Answer
A cursor is a database object that lets a program process a query result set one row at a time, instead of the usual set-based operation that acts on all matching rows at once.
Cursors are declared over a SELECT statement, then opened, fetched row-by-row into variables, and closed once processing finishes, giving procedural control inside stored procedures or scripts when row-by-row logic is unavoidable. Because they process one row at a time rather than letting the database engine optimize a whole set operation, cursors are typically much slower and more memory-intensive than equivalent set-based SQL, so they are used sparingly, only when a task truly cannot be expressed as a single set operation, such as complex row-dependent procedural logic. Most tasks that look like they need a cursor can actually be rewritten using JOINs, window functions, or UPDATE...FROM.
- Enables row-by-row procedural logic when needed
- Useful inside stored procedures for complex per-row rules
- Gives fine control over fetch, update, and close lifecycle
- A fallback when set-based SQL genuinely cannot express the logic
AI Mentor Explanation
A cursor is like a scorer going through the scorebook one delivery at a time, updating the running total after each ball rather than tallying the whole innings in one glance. It is slow and deliberate — open the book, read one ball, process it, move to the next, close the book when the innings ends. Set-based SQL instead is like reading the final scorecard summary directly; cursors exist for the rare cases where you truly need ball-by-ball procedural handling, not a summary.
Step-by-Step Explanation
Step 1
Declare the cursor
Define the cursor over a SELECT statement that returns the target result set.
Step 2
Open the cursor
Execute the query and position the cursor before the first row.
Step 3
Fetch rows in a loop
Retrieve one row at a time into variables and apply procedural logic.
Step 4
Close and deallocate
Release the cursor resources once all rows have been processed.
What Interviewer Expects
- Understanding of the declare-open-fetch-close lifecycle
- Awareness that cursors are slower than set-based operations
- Knowledge of when a cursor is genuinely necessary
- Ability to suggest a set-based alternative when possible
Common Mistakes
- Reaching for a cursor when a JOIN or window function would work
- Forgetting to close and deallocate the cursor, leaking resources
- Not understanding the performance cost versus set-based SQL
- Confusing a cursor with a temporary table
Best Answer (HR Friendly)
“A cursor lets me process query results one row at a time inside a stored procedure, which is useful for complex per-row logic that cannot be expressed as a single set-based query. That said, I only reach for cursors as a last resort since they are much slower than set-based operations like JOINs or window functions.”
Code Example
DECLARE emp_cursor CURSOR FOR
SELECT EmployeeID, Salary FROM Employees WHERE Department = 'Sales';
OPEN emp_cursor;
FETCH NEXT FROM emp_cursor INTO @EmpID, @Salary;
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE Employees SET Salary = @Salary * 1.1 WHERE EmployeeID = @EmpID;
FETCH NEXT FROM emp_cursor INTO @EmpID, @Salary;
END;
CLOSE emp_cursor;
DEALLOCATE emp_cursor;Follow-up Questions
- What are the performance drawbacks of using a cursor?
- How would you rewrite a cursor-based update using set-based SQL?
- What is the difference between a static and a dynamic cursor?
- When is a cursor genuinely unavoidable?
MCQ Practice
1. A cursor processes a result set how?
A cursor allows procedural, row-by-row processing of a query result set.
2. Compared to set-based SQL, cursors are generally?
Cursors process rows individually and typically perform much worse than equivalent set-based operations.
3. Which step must always follow after finishing cursor processing?
A cursor must be closed and deallocated to release its resources once processing is complete.
Flash Cards
Cursor — A database object for row-by-row processing of a result set.
Cursor lifecycle — Declare -> Open -> Fetch -> Close -> Deallocate.
Cursor performance — Generally much slower than equivalent set-based SQL.
When to use a cursor — Only when logic genuinely cannot be expressed as a set-based operation.