Answer.

Cursors in SQL allow for row-by-row traversal of a dataset and performing certain actions on each record, which is similar to iteration in programming. This is convenient for complex step-by-step logic that cannot be achieved with a single SQL query (for example, stepwise calculations with changing external state).

Cursor Example:

DECLARE my_cursor CURSOR FOR
SELECT id, balance FROM Accounts WHERE isActive = 1;

OPEN my_cursor;
DECLARE @id INT, @bal DECIMAL(10,2);
FETCH NEXT FROM my_cursor INTO @id, @bal;
WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE Accounts SET balance = @bal * 1.05 WHERE id = @id;
    FETCH NEXT FROM my_cursor INTO @id, @bal;
END
CLOSE my_cursor;
DEALLOCATE my_cursor;

Advantages:

Allow for the implementation of complex procedures where each row requires separate processing or checking of external conditions.
Convenient for integration with legacy systems or migrations where step-by-step control is needed.

Disadvantages and pitfalls:

Cursors are usually VERY slow: processing is done one by one rather than in a set-based manner;
Consume a lot of resources and can lock tables;
Don't scale for large volumes of data;
It's better to rewrite to set-based SQL or use batch processing.

Tricky Question.

Can a cursor be used inside a trigger? What consequences can this have for database performance?

Answer and Example: Using a cursor inside a trigger is possible but highly discouraged — each DML operation can launch a cursor for each affected row, leading to an avalanche of queries and locks.

CREATE TRIGGER UpdateBalance ON Accounts
AFTER INSERT
AS
DECLARE c CURSOR FOR SELECT id FROM inserted; -- bad!
OPEN c;
-- ...

History

Project: Retail Order Summary. There was a task to count stock balances if defects were found in a batch — subsequent batches require manual recounting through discount updates. A cursor was used to iterate through the batches. Later it was discovered that during multiple runs, the thread was locked for hours, server load grew exponentially, and lock contention led to failures.

History

Project: ERP, Data Migration. Error handling via cursor was added to the import processing procedure. It was not taken into account that for 5 million rows, the cursor worked 40 times slower than analogous batch processing using set-based UPDATE + CASE. Due to the slow migration, deadlines were shifted.

History

Project: Billing for a Financial Company. A cursor was added to the update trigger for the funds movement table to compute the new aggregate balance. In production, this led to "STOP THE WORLD" — inserting even one record slowed down the service due to the nested cursor being triggered for many rows.

Describe the features, advantages, and pitfalls of serial data processing using cursors (CURSOR) in SQL. When is it advisable to use cursors, and when should they be avoided in favor of set-based operations? Provide an example of using a cursor.

Answer.

Tricky Question.