A cursor loops row by row. A set-based operation processes the whole dataset at once. Most performance advice tells you to never touch a cursor, but that advice skips the handful of situations where a cursor is the only tool that works — or the only one that produces correct results.
The real question is not “are cursors evil?” It is “does my task require row-by-row processing, or is there a set-based alternative I am missing?” This post ranks the five most common cursor use cases from “legitimate” to “code smell,” so you know exactly where your query falls before you write a single FETCH NEXT.
1. The Legitimate Win: Dynamic SQL That Cannot Be Prewritten
Some problems cannot be expressed as a fixed query because the schema itself changes as you go. A cursor is the standard solution when you must generate and execute a different statement for each row.
Consider a maintenance job that rebuilds every fragmented index in a database. You query sys.dm_db_index_physical_stats, get a list of index names, then for each index name you build a dynamic ALTER INDEX ... REBUILD command. No set-based way exists to rebuild indexes from a list — you must execute each statement separately.
Another common case: archiving old rows into per-year archive tables. You loop through years, construct INSERT INTO archive_2020 SELECT ... WHERE year = 2020, execute it, then move to 2021. The target table name changes per iteration.
In both examples, the cursor is not a shortcut — it is the mechanism that makes the task possible. Set-based operations cannot execute dynamic statements. If you find yourself here, the cursor is the correct engineering choice.
2. The Justified Exception: Procedural Logic That SQL Cannot Express
Some business rules require steps that depend on values computed earlier in the same run. You need to process rows in a specific order, and each row’s calculation uses the previous row’s result as an input.
A canonical example: a running balance with per-transaction validation. Each transaction might be accepted or rejected based on whether the current balance covers it, and rejected transactions do not affect the balance. You cannot write that as a simple SUM() window function, because the decision for row N depends on the outcome of row N-1.
Another: hierarchical calculations where a parent’s value must be finalized before children are computed. Recursive CTEs handle some of these, but not when the logic involves branching decisions, state variables, or lookups inside a loop.
If you attempted a set-based version and found yourself writing convoluted self-joins that are impossible to verify for correctness, a cursor with local variables and explicit WHILE logic might be the clearer, more maintainable answer. The key test: does your calculation require state that carries across rows in a way that no LAG, LEAD, or SUM OVER can reproduce?
3. The Temporary Shortcut: Data Fix Scripts (With a Deadline)
Sometimes you inherit a messy dataset and need a one-time correction. The data is inconsistent, the business rules are complex, and the fix involves multiple tables updated in a strict sequence per logical entity.
You could write a set-based UPDATE with a CASE expression, but the logic is so tangled — validation, deduplication, logging each change, handling exceptions per row — that a cursor script that runs once and is then discarded feels pragmatic.
This is where the cursor is defensible but still not ideal. In practice, a cursor here trades long-term code quality for short-term speed of writing. The script runs overnight, fixes the data, and gets deleted. If it runs slowly, nobody cares — it only runs once.
The danger: “temporary” scripts have a habit of becoming permanent. Mark them clearly, put them in a separate folder, and set a reminder to review them in a month. If the fix needs to run repeatedly (monthly reconciliation, weekly imports), the cursor path becomes a liability — see the next item.
4. The Common Mistake: Using a Cursor Where a Window Function Works
This is the case that gives cursors a bad reputation. You want a running total, a rank within a group, the previous row’s value, or a cumulative count. Those are all expressible with SUM() OVER (ORDER BY ...), RANK() OVER (PARTITION BY ...), or LAG() — and they run orders of magnitude faster.
In testing, a window function on a table with 100,000 rows finishes in milliseconds. The equivalent cursor loops 100,000 times, fetches each row, updates a variable, and takes seconds — often minutes, depending on network latency and logging overhead.
The pattern to recognize: your cursor body contains an aggregate function or a comparison to a previously fetched value, and you are manually doing what OVER (PARTITION BY ... ORDER BY ...) does natively.
If you catch yourself writing FETCH NEXT FROM cursor INTO @prev_value just to compute @current_value - @prev_value, stop — that is what LAG() exists for. Nine times out of ten, a window function replaces the cursor entirely with cleaner, faster, and more readable code.
5. The Flat-Out Wrong Approach: Row-by-Row Inserts Into a Staging Table
The worst cursor pattern is the one that tries to replicate what a single INSERT ... SELECT with a JOIN could do. You loop through a source table, check a condition with an IF, and insert the matching row into a destination — one row at a time.
This is strictly worse than the set-based version in every measurable way. Slower, more code, harder to read, more prone to deadlocks (each INSERT acquires locks separately), and it disables query optimizer optimizations like bulk loading or minimally logged operations.
The set-based alternative is obvious: INSERT INTO destination SELECT columns FROM source WHERE condition. No cursor needed, no loop required.
If you wrote this cursor because the WHERE condition was complex, the answer is still the same — the WHERE clause handles complex logic. If you wrote it because you needed to do some per-row computation before inserting, re-read items 2 and 4 above. Cursors do not make computations easier; they make them slower.
The Decision Framework: Three Questions Before You Write a Cursor
Before you type DECLARE cursor_name CURSOR, answer these three questions in order:
Can I express this as a single statement? If yes — even if the statement is long or has a complex CASE — write that statement instead. It will be faster and easier to debug.
Can I express this with a window function or a recursive CTE? If your logic is “previous row,” “next row,” “rank within group,” or “cumulative total,” the answer is almost certainly yes. Write the window function version, test it against your expected output, and skip the cursor entirely.
Does each iteration execute a different statement (dynamic SQL) or maintain state that affects future iterations? If both answers are no, you do not need a cursor. If either answer is yes, then a cursor is justified — but also consider whether a WHILE loop with a temporary table would be cleaner, since it separates the loop logic from the cursor bookkeeping.
Performance Reality Check: What the Numbers Look Like
On a table with 50,000 rows, a window function SUM() OVER (PARTITION BY category ORDER BY sale_date) completes in roughly 50–100 milliseconds, depending on indexes. The equivalent cursor with a FETCH loop takes 5–15 seconds on the same hardware — two to three orders of magnitude slower.
The gap widens as table size grows because the cursor’s per-row overhead (fetch, context switch, lock acquisition) scales linearly while a set-based operation benefits from parallel execution and batch processing. On a million-row table, the cursor might take 10 minutes where the window function takes 2 seconds.
The only circumstances that flip this math: the per-row work is I/O-bound (like calling an external API or writing to a file), or the set-based version cannot be written at all. In those two cases, the cursor is not slower relative to the alternative — the alternative does not exist.
How to Make a Necessary Cursor Less Painful
If you pass the three-question test and keep the cursor, minimize the damage with these habits:
Declare the cursor as LOCAL FAST_FORWARD READ_ONLY when you only need forward-only, read-only access. This lets SQL Server use a more efficient mechanism than the default cursor type.
Keep the cursor’s SELECT narrow — fetch only the columns you need, and add a WHERE clause to filter rows before the loop starts, rather than checking conditions inside the loop.
Wrap the entire loop in a transaction if consistency matters, or use no transaction at all if each iteration is independent — committing per row is the worst of both worlds.
Move any computation that does not depend on the loop’s state outside the cursor. Precompute lookup values into local variables before the loop begins, and avoid re-querying the same reference data inside each iteration.
The Verdict, Ranked
Cursors have two legitimate uses: dynamic SQL execution and procedural state-based logic that no set-based construct can express. They have one defensible-but-temporary use: throwaway data fix scripts. They have two indefensible uses: any situation replaceable by a window function, and any row-by-row insert replicating an INSERT ... SELECT.
The skill is knowing which category your problem falls into before you write the code. Ask the three questions, time your alternative, and let the cursor prove it earns its keep — the default answer should always be the set-based one.
What operation are you looping through right now — is it a dynamic execution, a stateful calculation, or a running total you could write in one line? Describe your exact query and I will tell you which of the five categories it falls into and what the fastest rewrite looks like.