SQL Server Different Types of Cursors

Estimated study time: 8 minutes. Know your cursor options before you declare one.

SQL Server offers several cursor types, each trading off scrollability, sensitivity to data changes, and performance differently. You choose the type in the DECLARE CURSOR statement.

1. STATIC Cursor

Takes a full snapshot of the result set into tempdb when opened. Changes made to the underlying data afterward are never reflected.

DECLARE cur_static CURSOR STATIC FOR
SELECT EmployeeId, Name FROM Employees;

2. DYNAMIC Cursor

The opposite of static - reflects inserts, updates, and deletes made to the underlying tables while the cursor is open. This flexibility comes at a performance cost.

DECLARE cur_dynamic CURSOR DYNAMIC FOR
SELECT EmployeeId, Name FROM Employees;

3. FORWARD_ONLY Cursor

Can only move forward with FETCH NEXT - no scrolling backward. It's the default cursor type and, when combined with FAST_FORWARD, is the fastest option available.

DECLARE cur_forward CURSOR FORWARD_ONLY FOR
SELECT EmployeeId, Name FROM Employees;

4. KEYSET Cursor

Sits between static and dynamic. SQL Server stores a set of keys (not the full data) when the cursor opens. Updates to non-key columns in existing rows are visible, but new rows inserted by other sessions after the cursor opens are not.

DECLARE cur_keyset CURSOR KEYSET FOR
SELECT EmployeeId, Name FROM Employees;

5. FAST_FORWARD Cursor

A performance-optimized shortcut for a forward-only, read-only cursor. Use this whenever you just need to read rows in order without updating through the cursor.

DECLARE cur_fastforward CURSOR FAST_FORWARD FOR
SELECT EmployeeId, Name FROM Employees;

Comparison at a Glance