All 7 Cursors questions
More in Cursors
What are the basic steps required to declare, open, use, and properly clean up a cursor? What are the different cursor types available in SQL Server, such as static, dynamic, and forward only, and how do they differ? What is the performance impact of using a cursor compared to a set based operation on a large table, and why does this difference occur? What does the FAST_FORWARD cursor option do, and when should you use it? How would you rewrite a common cursor based row by row update into an equivalent, much faster set based UPDATE statement? In what rare situations might using a cursor still be a reasonable choice despite the general recommendation to avoid them?
Advertisement
SQL Server › Cursors

What is a cursor in SQL Server, and why do most developers try to avoid using them?

Beginner

Answer

A cursor lets you process a query's results one row at a time, similar to how you might loop through a list in a programming language, but most developers avoid them because SQL Server is optimized for set based operations that work on entire groups of rows at once, and row by row processing with cursors is usually much slower.
DECLARE order_cursor CURSOR FOR SELECT OrderId FROM Orders;
OPEN order_cursor;
FETCH NEXT FROM order_cursor INTO @OrderId;
WHILE @@FETCH_STATUS = 0
BEGIN
  -- process each order one at a time
  FETCH NEXT FROM order_cursor INTO @OrderId;
END;
CLOSE order_cursor;
DEALLOCATE order_cursor;
Real-world example A developer initially writes a cursor to update prices one product at a time, then rewrites it as a single set based UPDATE statement, seeing a dramatic performance improvement on a table with thousands of rows.

Common follow-ups: What is a set based alternative to a common cursor based task?;When, if ever, is using a cursor actually the right choice?

Stored Procedures & Functions;Query Optimization & Plans
All 7 Cursors questions