SQL - Cursors Tutorial
Cursors provide a mechanism to traverse result sets one row at a time, enabling procedural logic within SQL Server. While SQL excels at set-based operations, certain scenarios require iterative...
Key Insights
- Cursors enable row-by-row processing of SQL result sets when set-based operations are insufficient, though they sacrifice performance for procedural control
- Different cursor types (FORWARD_ONLY, STATIC, DYNAMIC, KEYSET) offer varying levels of data sensitivity and scrolling capabilities with corresponding performance trade-offs
- Modern SQL development favors set-based alternatives like CTEs, window functions, and table variables over cursors except when interfacing with external systems or complex row-level logic
Understanding SQL Cursors
Cursors provide a mechanism to traverse result sets one row at a time, enabling procedural logic within SQL Server. While SQL excels at set-based operations, certain scenarios require iterative processing where cursors become necessary.
A cursor acts as a pointer to a specific row within a result set. You declare it, open it, fetch rows sequentially, and close it when finished. This pattern mirrors file handling in traditional programming languages.
-- Basic cursor structure
DECLARE @ProductID INT
DECLARE @ProductName NVARCHAR(100)
DECLARE product_cursor CURSOR FOR
SELECT ProductID, ProductName
FROM Products
WHERE CategoryID = 1
OPEN product_cursor
FETCH NEXT FROM product_cursor INTO @ProductID, @ProductName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Product: ' + @ProductName
FETCH NEXT FROM product_cursor INTO @ProductID, @ProductName
END
CLOSE product_cursor
DEALLOCATE product_cursor
Cursor Types and Their Characteristics
SQL Server offers four cursor types, each with distinct behavior regarding data sensitivity and navigation.
FORWARD_ONLY cursors (default) allow single-direction traversal and offer the best performance. They don’t reflect changes made after opening.
DECLARE forward_cursor CURSOR FORWARD_ONLY FOR
SELECT OrderID, OrderDate, TotalAmount
FROM Orders
WHERE OrderDate >= '2024-01-01'
OPEN forward_cursor
-- Can only FETCH NEXT
STATIC cursors create a temporary snapshot in tempdb. Changes to underlying data aren’t visible, making them predictable but resource-intensive.
DECLARE static_cursor CURSOR STATIC FOR
SELECT CustomerID, LastName, Email
FROM Customers
OPEN static_cursor
-- Data frozen at cursor open time
DYNAMIC cursors reflect all changes to data, including inserts, updates, and deletes. They provide real-time data but with performance overhead.
DECLARE dynamic_cursor CURSOR DYNAMIC FOR
SELECT InventoryID, Quantity
FROM Inventory
WHERE WarehouseID = 5
OPEN dynamic_cursor
-- Sees all data modifications
KEYSET cursors use keys to identify rows. They detect updates and deletes but not inserts. They balance performance and data sensitivity.
DECLARE keyset_cursor CURSOR KEYSET FOR
SELECT AccountID, Balance
FROM Accounts
WHERE AccountType = 'Checking'
OPEN keyset_cursor
-- Detects updates/deletes, not inserts
Practical Cursor Implementation
Consider a scenario where you need to process orders sequentially, applying business logic that varies based on order attributes and external API calls.
CREATE PROCEDURE ProcessPendingOrders
AS
BEGIN
DECLARE @OrderID INT
DECLARE @CustomerID INT
DECLARE @TotalAmount DECIMAL(10,2)
DECLARE @ShippingMethod VARCHAR(50)
DECLARE @ProcessingFee DECIMAL(10,2)
DECLARE order_cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT OrderID, CustomerID, TotalAmount, ShippingMethod
FROM Orders
WHERE OrderStatus = 'Pending'
ORDER BY OrderDate
OPEN order_cursor
FETCH NEXT FROM order_cursor
INTO @OrderID, @CustomerID, @TotalAmount, @ShippingMethod
WHILE @@FETCH_STATUS = 0
BEGIN
-- Calculate processing fee based on complex logic
SET @ProcessingFee = CASE
WHEN @TotalAmount < 50 THEN 5.00
WHEN @ShippingMethod = 'Express' THEN @TotalAmount * 0.15
ELSE @TotalAmount * 0.05
END
-- Update order with calculated fee
UPDATE Orders
SET ProcessingFee = @ProcessingFee,
OrderStatus = 'Processing',
ProcessedDate = GETDATE()
WHERE OrderID = @OrderID
-- Log processing
INSERT INTO OrderProcessingLog (OrderID, ProcessedBy, ProcessingFee)
VALUES (@OrderID, SYSTEM_USER, @ProcessingFee)
FETCH NEXT FROM order_cursor
INTO @OrderID, @CustomerID, @TotalAmount, @ShippingMethod
END
CLOSE order_cursor
DEALLOCATE order_cursor
END
Cursor Scope and Concurrency
Cursor scope determines visibility and lifetime. LOCAL cursors exist only within their batch or stored procedure, while GLOBAL cursors remain accessible across connections.
-- Local cursor (recommended)
DECLARE local_cursor CURSOR LOCAL FOR
SELECT EmployeeID, Salary
FROM Employees
-- Global cursor (use sparingly)
DECLARE global_cursor CURSOR GLOBAL FOR
SELECT DepartmentID, Budget
FROM Departments
Concurrency options control locking behavior:
-- Read-only cursor (no locks)
DECLARE readonly_cursor CURSOR FOR
SELECT * FROM Products
FOR READ ONLY
-- Optimistic concurrency
DECLARE optimistic_cursor CURSOR OPTIMISTIC FOR
SELECT * FROM Inventory
-- Scroll locks (pessimistic)
DECLARE locked_cursor CURSOR SCROLL_LOCKS FOR
SELECT * FROM Orders
Advanced Cursor Operations
Scrollable cursors support bidirectional navigation using FETCH options:
DECLARE scroll_cursor CURSOR SCROLL FOR
SELECT ProductID, ProductName, UnitPrice
FROM Products
OPEN scroll_cursor
FETCH FIRST FROM scroll_cursor -- First row
FETCH LAST FROM scroll_cursor -- Last row
FETCH ABSOLUTE 5 FROM scroll_cursor -- 5th row
FETCH RELATIVE -2 FROM scroll_cursor -- 2 rows back
FETCH PRIOR FROM scroll_cursor -- Previous row
Positioned Updates and Deletes
Cursors enable updates and deletes at the current cursor position using WHERE CURRENT OF:
DECLARE @ProductID INT
DECLARE @CurrentPrice DECIMAL(10,2)
DECLARE @NewPrice DECIMAL(10,2)
DECLARE price_cursor CURSOR FOR
SELECT ProductID, UnitPrice
FROM Products
WHERE CategoryID = 3
FOR UPDATE OF UnitPrice
OPEN price_cursor
FETCH NEXT FROM price_cursor INTO @ProductID, @CurrentPrice
WHILE @@FETCH_STATUS = 0
BEGIN
-- Apply 10% discount
SET @NewPrice = @CurrentPrice * 0.90
UPDATE Products
SET UnitPrice = @NewPrice,
LastModified = GETDATE()
WHERE CURRENT OF price_cursor
FETCH NEXT FROM price_cursor INTO @ProductID, @CurrentPrice
END
CLOSE price_cursor
DEALLOCATE price_cursor
When to Avoid Cursors
Most cursor operations can be replaced with set-based alternatives that perform significantly better:
-- Instead of cursor to update prices
DECLARE cursor_cursor CURSOR FOR SELECT ProductID, UnitPrice FROM Products
-- Complex cursor loop here...
-- Use set-based update
UPDATE Products
SET UnitPrice = UnitPrice * 0.90,
LastModified = GETDATE()
WHERE CategoryID = 3
-- Instead of cursor for running totals
-- Use window functions
SELECT
OrderID,
OrderDate,
Amount,
SUM(Amount) OVER (ORDER BY OrderDate) AS RunningTotal
FROM Orders
-- Instead of cursor for conditional logic
-- Use CASE expressions
UPDATE Customers
SET DiscountRate = CASE
WHEN TotalPurchases > 10000 THEN 0.15
WHEN TotalPurchases > 5000 THEN 0.10
WHEN TotalPurchases > 1000 THEN 0.05
ELSE 0.00
END
Performance Considerations
Cursors consume memory and hold locks longer than set-based operations. Monitor cursor performance using these practices:
-- Use FAST_FORWARD for best performance
DECLARE efficient_cursor CURSOR FAST_FORWARD FOR
SELECT OrderID FROM Orders
-- Limit result set size
DECLARE filtered_cursor CURSOR FOR
SELECT TOP 1000 * FROM LargeTable
WHERE ProcessedDate IS NULL
-- Close and deallocate promptly
CLOSE cursor_name
DEALLOCATE cursor_name
Check for unclosed cursors:
SELECT
name,
creation_time,
is_open
FROM sys.dm_exec_cursors(0)
WHERE is_open = 1
Cursors remain valuable for specific use cases: interfacing with external systems, complex row-by-row business logic, and administrative tasks. However, exhaust set-based alternatives first. When cursors are necessary, choose the appropriate type, minimize scope, and ensure proper cleanup to maintain optimal database performance.