What Is a Cursor in SQL Server, and When to Avoid One

A cursor in SQL Server walks through a result one row at a time, the way a loop does in any other language. It is the most natural thing in the world for a programmer to reach for, and it is usually the wrong tool. Here is the measurement that explains why.

What It Looks Like

A cursor has five steps and they are always the same. Declare it, open it, fetch, loop while fetching works, then close and deallocate.

DECLARE @n int = 0, @a money;

DECLARE c CURSOR LOCAL FAST_FORWARD FOR
    SELECT amount FROM dbo.Orders WHERE id <= 20000;

OPEN c;
FETCH NEXT FROM c INTO @a;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @n += 1;
    FETCH NEXT FROM c INTO @a;
END
CLOSE c;
DEALLOCATE c;

That counts 20,000 rows. Here is the same count written as one statement:

SELECT COUNT(*) FROM dbo.Orders WHERE id <= 20000;

The Number

I timed both on SQL Server 2025, same table, same 20,000 rows, one after the other:

cursor_rows  cursor_ms  setbased_rows  setbased_ms
20000        260        20000          4

260 milliseconds against 4. Sixty five times slower for the same answer, and this is the friendliest cursor I could write. I used LOCAL and FAST_FORWARD, which is the cheapest kind. A default cursor is slower still.

Two things to be fair about. This is one measurement on one machine, and the gap changes with the work inside the loop. What does not change is the shape: the cursor pays a cost per row, and the set based query pays once.

Why the Gap Exists

SQL is a language for describing a result, not for describing steps. You say what you want and the optimizer decides how to get it, including whether to use several processors.

A cursor takes that away. You have told the engine the order to do things in, so it does them in that order, one at a time, and every fetch is its own small piece of work. Twenty thousand rows means twenty thousand of them.

Thinking in Sets

Most cursors people write are a loop that could be one statement. The pattern is nearly always the same: fetch a row, work out a value, update something.

-- what the loop was doing
UPDATE dbo.Orders SET amount = amount * 1.1 WHERE city = 'Dublin';

When you catch yourself writing a loop over rows, the question to ask is what the whole set has in common. Usually the answer turns the loop into a WHERE clause, a CASE expression, or a window function.

When a Cursor Is Right

It is not never. Three cases come up in real work.

Each row causes something outside the database. Calling a procedure per row, running a maintenance command per database, sending each row somewhere. There is nothing to set-ify.

Each row genuinely depends on the last. Some running calculations resist set based rewriting, though window functions have removed most of these.

Breaking a huge change into pieces on purpose. Updating fifty million rows in one statement holds locks for a long time and makes the log enormous. Doing it in batches is slower in total and much kinder to everybody else. That is a loop, and it is the right call.

If You Must

Declare it LOCAL so it disappears with the batch. Add FAST_FORWARD for a read-only forward-only walk, which is the cheapest option. Select only the columns you use. Always CLOSE and DEALLOCATE, including in your error handling, because a cursor left open holds resources.

When somebody sends me a slow procedure, a cursor is one of the first things I look for. It is rarely the only problem, and it is usually the easiest one to explain.

A cursor is not a bad habit, it is the right answer to a question you should check you are actually asking.

This post was rewritten from scratch in September 2026. The original, published on 2012-07-31, was a short announcement about something that no longer exists. The address is the same, the subject is now a basic idea worth keeping.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.