PrismitaLEARN · PRACTICE · GROW

What is a cursor in T-SQL, and why should you usually avoid it?

SQL Server T-SQL Intermediate 1–2 Yrs2–3 Yrs3–5 Yrs Conceptual 1 min read

Quick Answer

A cursor processes a result set one row at a time in a loop — the opposite of SQL's set-based nature. It's usually much slower and holds locks/resources longer than an equivalent set-based statement. Avoid it when a single UPDATE/INSERT...SELECT/MERGE can do the job.

A cursor walks a result set row by row in a loop. SQL is designed to work on sets, so a cursor is nearly always slower and holds locks and resources longer than the equivalent single statement. Ninety percent of the time, what someone's doing with a cursor is really an UPDATE ... WHERE or an INSERT ... SELECT. Where a cursor is genuinely reasonable is when you have to call a stored procedure once per row, or you're doing an administrative task iterating over objects, or you need tightly controlled batching of a massive operation.

  • Set-based: UPDATE Orders SET Status = 'Stale' WHERE UpdatedAt < @cutoff; — one statement, one plan.
  • Cursor version: declare, open, fetch, loop, update per row, close, deallocate — 100× the work for the same result.
  • Legit uses: calling a stored procedure per row, iterating over databases/objects for admin tasks, complex running calculations that can't be expressed as a window function, controlled batching of a huge operation.
  • If you must loop, a WHILE loop over a batched DELETE TOP (n) is often better than a true cursor.
All SQL Server questions More on T-SQL