All 7 Cursors questions
More in Cursors
What is a cursor in SQL Server, and why do most developers try to avoid using them? 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 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 the performance impact of using a cursor compared to a set based operation on a large table, and why does this difference occur?

Intermediate

Answer

A cursor processes one row at a time, incurring the overhead of fetching and processing each individual row separately, while a set based operation lets SQL Server's query optimizer work on the entire result set at once, taking advantage of indexes, parallel processing, and efficient execution plans that a row by row loop cannot benefit from.
-- Cursor: processes 100,000 rows one at a time, each with its own overhead
-- Set based: processes all 100,000 rows in a single optimized operation
UPDATE Orders SET Status = 'Archived' WHERE OrderDate < '2020-01-01';
Real-world example A reporting team notices a cursor based nightly job taking over an hour to process a large orders table, and after converting it to a set based approach, the same task completes in just a few seconds.

Common follow-ups: Can a cursor ever take advantage of parallel query execution?;How much slower is a typical cursor compared to an equivalent set based statement on a large table?

Query Optimization & Plans;Indexes
All 7 Cursors questions