SQL Server Cursors: Syntax, Options, Performance and Set-Based Alternatives

A cursor lets T-SQL process a result set one row at a time, the way most programming languages loop over a collection. That’s why developers reach for it, and why it’s behind so many slow batch jobs: SQL Server is built to process sets, and a cursor turns one statement over a million rows into a million separate statements.

Cursors aren’t forbidden. For administrative scripts that loop over databases, or logic that really must run row by row, they’re the simplest correct tool. This guide covers cursor syntax and the options that matter, why cursors are slow, four common cursor patterns rewritten as set-based queries, the right way to kill all sessions on a database, and how to write a cursor properly when you do need one.

Basic Cursor Syntax

Every cursor follows the same five steps: declare it with a query, open it, fetch rows in a loop until @@FETCH_STATUS is no longer 0, close it and deallocate it.

DECLARE @AccountID int;

DECLARE account_cursor CURSOR LOCAL FAST_FORWARD FOR
    SELECT AccountID
    FROM dbo.Accounts
    WHERE IsActive = 1;

OPEN account_cursor;
FETCH NEXT FROM account_cursor INTO @AccountID;

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC dbo.RecalculateAccount @AccountID = @AccountID;  -- the per-row work
    FETCH NEXT FROM account_cursor INTO @AccountID;
END;

CLOSE account_cursor;
DEALLOCATE account_cursor;

Cursor Options That Matter

A cursor declared without options uses defaults that suit almost no one: it’s global (unless the database option CURSOR_DEFAULT is set to LOCAL) and dynamic, so it re-checks the underlying data on every fetch. The options below change that.

Why Cursors Are Slow

• One execution per row. Every FETCH and every statement inside the loop is executed separately, with its own overhead, instead of one plan processing all rows.
• No set-based optimizations. Joins, hash aggregates, batch mode and parallelism can’t help a loop that touches one row at a time.
• Logging per statement. A million single-row inserts in autocommit mode means a million log flushes, each waiting for the disk.
• Longer locks and transactions. If the loop runs inside one transaction, locks accumulate for its whole duration and block other sessions.
• tempdb work for STATIC and KEYSET cursors, which copy data before the first fetch.

The difference isn’t a few percent. Rewriting a cursor as a single statement routinely turns minutes or hours into seconds.

Pattern 1: Row-by-Row Insert

A cursor that reads rows from one table and inserts them into another is the most common pattern, and the easiest to replace:

-- Cursor version: one INSERT per row
DECLARE @ID int, @First varchar(50), @Last varchar(50);
DECLARE c CURSOR LOCAL FAST_FORWARD FOR
    SELECT EmployeeID, FirstName, LastName FROM dbo.Employee;
OPEN c;
FETCH NEXT FROM c INTO @ID, @First, @Last;
WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO dbo.EmployeeArchive (EmployeeID, FullName)
    VALUES (@ID, @First + ' ' + @Last);
    FETCH NEXT FROM c INTO @ID, @First, @Last;
END;
CLOSE c; DEALLOCATE c;

-- Set-based version: one statement
INSERT INTO dbo.EmployeeArchive (EmployeeID, FullName)
SELECT EmployeeID, CONCAT(FirstName, ' ', LastName)
FROM dbo.Employee;

Pattern 2: Update Each Row from Another Table

-- Instead of looping over orders and looking up the customer's discount:
UPDATE o
SET    o.DiscountRate = c.DiscountRate
FROM   dbo.Orders    AS o
JOIN   dbo.Customers AS c ON c.CustomerID = o.CustomerID
WHERE  o.OrderDate >= '20260101';

Pattern 3: Running Totals

Running totals were a classic reason for cursors before window functions. Since SQL Server 2012, they’re a single query:

SELECT
    AccountID,
    TransactionDate,
    Amount,
    SUM(Amount) OVER (PARTITION BY AccountID
                      ORDER BY TransactionDate, TransactionID
                      ROWS UNBOUNDED PRECEDING) AS RunningBalance
FROM dbo.Transactions;

Specify ROWS UNBOUNDED PRECEDING explicitly. Without it, the default frame is RANGE, which is slower and treats rows with the same ORDER BY value as a group.

Pattern 4: Splitting Text and Counting Words

Parsing text in a loop, character by character or word by word, is another cursor favorite. With STRING_SPLIT (SQL Server 2016 and later, compatibility level 130 or higher) it’s a grouped query:

-- The three most frequent words across all rows
SELECT TOP (3)
    LOWER(s.value) AS word,
    COUNT(*)       AS occurrences
FROM dbo.Notes AS n
CROSS APPLY STRING_SPLIT(n.NoteText, ' ') AS s
WHERE LEN(s.value) > 2
GROUP BY LOWER(s.value)
ORDER BY occurrences DESC;

Administrative Loops: Generate the Statements Instead

DBA scripts often loop over tables or databases to run a command for each one. Usually it’s clearer to generate the statements with one query, review them, and then run them:

-- Tables starting with "tmp_" that are older than one month
SELECT STRING_AGG(
           CONVERT(nvarchar(max), N'DROP TABLE ' + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N';'),
           CHAR(13) + CHAR(10)) AS drop_script
FROM sys.tables  AS t
JOIN sys.schemas AS s ON s.schema_id = t.schema_id
WHERE t.name LIKE N'tmp[_]%'
  AND t.create_date < DATEADD(MONTH, -1, SYSDATETIME());

Generating the script first also gives you a record of what was changed. Old tables that nobody dares to drop are a governance problem as much as a technical one; see what is data governance.

When a loop really is needed, for example running DBCC CHECKDB on every database in turn, a cursor over sys.databases is perfectly fine. The number of iterations is small and each one is a large, set-based operation.

Example: Last Successful CHECKDB Date for Every Database

The date of each database’s last successful DBCC CHECKDB is stored in the dbi_dbccLastKnownGood field on the database’s boot page and can be read with DBCC DBINFO. Because the command runs in the context of a database, you have to iterate over the databases one by one; this is a typical DBA task where a loop genuinely makes sense.

Below, the same job is done first with a cursor and then with a WHILE loop. Both scripts flag CHECKDB runs that are older than the number of days you set, or that have never run.

1. With a Cursor

SET NOCOUNT ON;

DECLARE @ThresholdDay int = 7;   -- CHECKDB older than this is flagged as overdue
DECLARE @DatabaseName sysname,
        @sql nvarchar(max);

IF OBJECT_ID(N'tempdb..#DBInfo') IS NOT NULL DROP TABLE #DBInfo;
CREATE TABLE #DBInfo (ParentObject nvarchar(255), [Object] nvarchar(255),
                      Field nvarchar(255), [Value] nvarchar(4000));

IF OBJECT_ID(N'tempdb..#Result') IS NOT NULL DROP TABLE #Result;
CREATE TABLE #Result (DatabaseName sysname, LastGoodCheckDb datetime);

DECLARE db_cursor CURSOR LOCAL FAST_FORWARD FOR
    SELECT name
    FROM sys.databases
    WHERE state_desc = N'ONLINE'
      AND is_read_only = 0
      AND database_id > 4          -- exclude system databases
    ORDER BY name;

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @DatabaseName;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = N'USE ' + QUOTENAME(@DatabaseName) + N'; DBCC DBINFO() WITH TABLERESULTS;';
    INSERT INTO #DBInfo EXEC sys.sp_executesql @sql;

    INSERT INTO #Result (DatabaseName, LastGoodCheckDb)
    SELECT TOP (1) @DatabaseName, CONVERT(datetime, [Value])
    FROM #DBInfo
    WHERE Field = N'dbi_dbccLastKnownGood';

    TRUNCATE TABLE #DBInfo;
    FETCH NEXT FROM db_cursor INTO @DatabaseName;
END

CLOSE db_cursor;
DEALLOCATE db_cursor;

SELECT DatabaseName,
       LastGoodCheckDb,
       CASE WHEN LastGoodCheckDb < '19000102' THEN N'Never run'
            WHEN LastGoodCheckDb < DATEADD(DAY, -@ThresholdDay, GETDATE()) THEN N'Overdue'
            ELSE N'OK' END AS CheckDbStatus
FROM #Result
ORDER BY LastGoodCheckDb;

DROP TABLE #DBInfo;
DROP TABLE #Result;

The database name is wrapped with QUOTENAME, so databases whose names contain spaces, hyphens or a ] character don’t break the script. Only DBCC DBINFO needs the database context, so only that line goes into dynamic SQL; processing the results stays ordinary T-SQL. For a database where CHECKDB has never run, the field returns 1900-01-01, and the script flags that separately.

2. With a WHILE Loop and a Queue Table

SET NOCOUNT ON;

DECLARE @ThresholdDay int = 7;
DECLARE @DatabaseName sysname,
        @sql nvarchar(max);

IF OBJECT_ID(N'tempdb..#DBInfo') IS NOT NULL DROP TABLE #DBInfo;
CREATE TABLE #DBInfo (ParentObject nvarchar(255), [Object] nvarchar(255),
                      Field nvarchar(255), [Value] nvarchar(4000));

IF OBJECT_ID(N'tempdb..#Result') IS NOT NULL DROP TABLE #Result;
CREATE TABLE #Result (DatabaseName sysname, LastGoodCheckDb datetime);

-- Queue: 0 = pending, 1 = done, 2 = error
IF OBJECT_ID(N'tempdb..#Queue') IS NOT NULL DROP TABLE #Queue;
SELECT name AS DatabaseName,
       CAST(0 AS tinyint) AS QueueStatus,
       CAST(NULL AS nvarchar(4000)) AS ErrorMessage
INTO #Queue
FROM sys.databases
WHERE state_desc = N'ONLINE'
  AND is_read_only = 0
  AND database_id > 4;

WHILE EXISTS (SELECT 1 FROM #Queue WHERE QueueStatus = 0)
BEGIN
    SELECT TOP (1) @DatabaseName = DatabaseName
    FROM #Queue
    WHERE QueueStatus = 0
    ORDER BY DatabaseName;

    BEGIN TRY
        SET @sql = N'USE ' + QUOTENAME(@DatabaseName) + N'; DBCC DBINFO() WITH TABLERESULTS;';
        INSERT INTO #DBInfo EXEC sys.sp_executesql @sql;

        INSERT INTO #Result (DatabaseName, LastGoodCheckDb)
        SELECT TOP (1) @DatabaseName, CONVERT(datetime, [Value])
        FROM #DBInfo
        WHERE Field = N'dbi_dbccLastKnownGood';

        UPDATE #Queue SET QueueStatus = 1 WHERE DatabaseName = @DatabaseName;
    END TRY
    BEGIN CATCH
        -- Flag the failing database and move on to the next one
        UPDATE #Queue
        SET QueueStatus = 2, ErrorMessage = ERROR_MESSAGE()
        WHERE DatabaseName = @DatabaseName;
    END CATCH;

    TRUNCATE TABLE #DBInfo;
END

SELECT DatabaseName,
       LastGoodCheckDb,
       CASE WHEN LastGoodCheckDb < '19000102' THEN N'Never run'
            WHEN LastGoodCheckDb < DATEADD(DAY, -@ThresholdDay, GETDATE()) THEN N'Overdue'
            ELSE N'OK' END AS CheckDbStatus
FROM #Result
ORDER BY LastGoodCheckDb;

-- Databases that couldn't be read, and why
SELECT DatabaseName, ErrorMessage FROM #Queue WHERE QueueStatus = 2;

DROP TABLE #Queue;
DROP TABLE #DBInfo;
DROP TABLE #Result;

Which One Is More Flexible?

Both scripts produce the same result; the difference is where the state of the loop lives.

With a cursor, the state lives in the cursor itself: the list comes from the query at OPEN, FETCH moves through it in order, and nothing is left behind when the loop ends. For a short, readable, one-off scan, it does the job with the least code, and LOCAL FAST_FORWARD keeps the overhead low.

In the WHILE version, the state lives in a table, and you can do whatever you need with that table:

• Error handling: when a database fails, you flag its row as an error, keep the message and continue with the next database. At the end, a single query shows which databases were skipped and why.
• Resuming where it stopped: if you keep the queue in a permanent table instead of a temp table, a job that was interrupted processes only the pending databases when it restarts. For CHECKDB or index maintenance jobs that run through the night, this makes a big difference.
• Order and priority: change the ORDER BY to process critical or small databases first, or add a priority column to the table.
• Timing and logging: add columns such as start time, end time and attempt count to record how long each step took.
• Splitting the work: in a permanent queue table, sessions can “claim” rows with UPDATE ... OUTPUT, so the work can be spread across several sessions or jobs.

That flexibility comes at a price: progress is entirely up to you. If a row is never marked, for example because the UPDATE is skipped on error, the WHILE EXISTS condition never becomes false and the loop spins on the same database forever. That’s why the script above marks the row in both the TRY and the CATCH block.

Rule of thumb: for simple, one-off scans, a LOCAL FAST_FORWARD cursor is enough. For maintenance jobs that need error tolerance, restartability or logging, a queue table with a WHILE loop is the better choice.

You May Not Need a Loop for This at All

In SQL Server 2016 SP2, SQL Server 2017 CU9 and later, the last successful CHECKDB time can be read directly with DATABASEPROPERTYEX. The job then shrinks to a single set-based query, in line with the main idea of this guide:

SELECT name AS DatabaseName,
       DATABASEPROPERTYEX(name, 'LastGoodCheckDbTime') AS LastGoodCheckDb
FROM sys.databases
WHERE state_desc = N'ONLINE'
  AND database_id > 4
ORDER BY LastGoodCheckDb;

The loop-based approaches are still useful on older versions, when you need other fields returned by DBCC DBINFO, or when you really have to run a command in every database. Keep in mind that DBCC DBINFO is undocumented and its output format can change between versions.

Killing All Sessions on a Database

A popular cursor script reads sp_who into a temp table and runs KILL for each session in a database. You don’t need a cursor for this. To take a database for exclusive use, let SQL Server do it:

-- Disconnect everyone and roll back their open transactions
ALTER DATABASE SalesDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
-- ... maintenance ...
ALTER DATABASE SalesDB SET MULTI_USER;

If you only need to disconnect sessions and keep the database available, generate the KILL statements from the session DMV:

DECLARE @sql nvarchar(max);

SELECT @sql = STRING_AGG(CONVERT(nvarchar(max), N'KILL ' + CONVERT(nvarchar(10), session_id) + N';'), N' ')
FROM sys.dm_exec_sessions
WHERE database_id = DB_ID(N'SalesDB')
  AND session_id <> @@SPID
  AND is_user_process = 1;

PRINT @sql;           -- review first
-- EXEC sys.sp_executesql @sql;

Loops That Are Fine: Batching Large Changes

Not every loop is a cursor problem. Deleting or updating tens of millions of rows in one statement can fill the transaction log, block the table for a long time and take hours to roll back if cancelled. A loop that processes the change in batches is the right tool here:

DECLARE @rows int = 1;

WHILE @rows > 0
BEGIN
    DELETE TOP (5000)
    FROM dbo.AuditLog
    WHERE LoggedAt < DATEADD(YEAR, -2, SYSDATETIME());

    SET @rows = @@ROWCOUNT;
END;

Each batch is a short, set-based transaction. Locks are released between batches, the log can be reused after log backups, and stopping the job only rolls back the current batch. An index on the filter column keeps each batch from scanning the table.

If You Must Use a Cursor

• Declare it LOCAL FAST_FORWARD (or LOCAL STATIC READ_ONLY when the loop changes the source).
• Keep the cursor’s result set small; filter in the cursor query, not inside the loop.
• Don’t wrap the whole loop in one transaction unless it must be atomic.
• Always CLOSE and DEALLOCATE, including in a CATCH block.
• Avoid nested cursors; they’re almost always a join in disguise.
• Measure: if the loop body is a single statement, the whole loop can probably be one statement too.