1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

-- ANTI-PATTERN: Cursor to update salaries
DECLARE salary_cursor CURSOR FOR SELECT emp_id, salary FROM employees;
OPEN salary_cursor;
FETCH NEXT FROM salary_cursor INTO @emp_id, @salary;
WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE employees SET salary = @salary * 1.10 WHERE emp_id = @emp_id;
    FETCH NEXT FROM salary_cursor INTO @emp_id, @salary;
END
CLOSE salary_cursor;
DEALLOCATE salary_cursor;
-- Time: 45 seconds for 100K rows
 
-- CORRECT: Set-based update
UPDATE employees SET salary = salary * 1.10;
-- Time: 0.5 seconds for 100K rows
 
-- ANTI-PATTERN: Cursor to calculate running totals
DECLARE @running_total DECIMAL(15,2) = 0;
DECLARE total_cursor CURSOR FOR 
    SELECT order_id, amount FROM orders ORDER BY order_date;
-- ... loop to accumulate @running_total ...
 
-- CORRECT: Window function (SQL Server 2012+, Oracle, PostgreSQL)
SELECT order_id, amount,
       SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;
 
-- ANTI-PATTERN: Cursor to delete duplicates
DECLARE @last_email VARCHAR(200) = '';
DECLARE dup_cursor CURSOR FOR 
    SELECT customer_id, email FROM customers ORDER BY email, customer_id;
-- ... loop checking if email = @last_email ...
 
-- CORRECT: CTE with ROW_NUMBER
WITH numbered AS (
    SELECT customer_id, email,
           ROW_NUMBER() OVER (PARTITION BY email ORDER BY customer_id) AS rn
    FROM customers
)
DELETE FROM numbered WHERE rn > 1;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116

-- USE CASE 1: External API calls
-- Sending emails or calling external services for each row
-- Cannot be parallelized or batched due to external system constraints
 
DECLARE @customer_id INT, @email VARCHAR(200), @notification_type VARCHAR(50);
 
DECLARE notification_cursor CURSOR FAST_FORWARD FOR
    SELECT customer_id, email, notification_type
    FROM pending_notifications
    WHERE sent_date IS NULL
    ORDER BY priority DESC;
 
OPEN notification_cursor;
FETCH NEXT FROM notification_cursor INTO @customer_id, @email, @notification_type;
 
WHILE @@FETCH_STATUS = 0
BEGIN
    -- Call external email service (row-by-row is unavoidable)
    EXEC dbo.SendEmailViaExternalAPI @email, @notification_type;
    
    UPDATE pending_notifications 
    SET sent_date = GETDATE() 
    WHERE customer_id = @customer_id AND notification_type = @notification_type;
    
    FETCH NEXT FROM notification_cursor INTO @customer_id, @email, @notification_type;
END
 
CLOSE notification_cursor;
DEALLOCATE notification_cursor;
 
-- USE CASE 2: Complex inter-row dependent logic
-- Inventory allocation where each allocation affects subsequent availability
-- Cannot be expressed as single UPDATE
 
DECLARE @order_id INT, @product_id INT, @qty_requested INT;
DECLARE @qty_available INT;
 
DECLARE allocation_cursor CURSOR FOR
    SELECT o.order_id, ol.product_id, ol.quantity
    FROM orders o
    INNER JOIN order_lines ol ON o.order_id = ol.order_id
    WHERE o.status = 'pending_allocation'
    ORDER BY o.priority, o.order_date  -- Priority matters!
    FOR UPDATE;
 
OPEN allocation_cursor;
FETCH NEXT FROM allocation_cursor INTO @order_id, @product_id, @qty_requested;
 
WHILE @@FETCH_STATUS = 0
BEGIN
    -- Get CURRENT available quantity (affected by previous allocations)
    SELECT @qty_available = available_quantity 
    FROM inventory WHERE product_id = @product_id;
    
    IF @qty_available >= @qty_requested
    BEGIN
        -- Allocate (reduces availability for subsequent orders)
        UPDATE inventory 
        SET available_quantity = available_quantity - @qty_requested,
            allocated_quantity = allocated_quantity + @qty_requested
        WHERE product_id = @product_id;
        
        UPDATE order_lines SET allocated = 1 
        WHERE CURRENT OF allocation_cursor;
    END
    ELSE
    BEGIN
        -- Partial allocation or wait
        UPDATE order_lines SET allocation_status = 'pending_stock'
        WHERE CURRENT OF allocation_cursor;
    END
    
    FETCH NEXT FROM allocation_cursor INTO @order_id, @product_id, @qty_requested;
END
 
CLOSE allocation_cursor;
DEALLOCATE allocation_cursor;
 
-- USE CASE 3: Batch commit processing
-- Process millions of rows without holding huge transaction
 
DECLARE @batch_size INT = 10000;
DECLARE @processed INT = 0;
DECLARE @transaction_id BIGINT;
 
DECLARE cleanup_cursor CURSOR GLOBAL FOR  -- GLOBAL survives commit
    SELECT transaction_id 
    FROM old_transactions 
    WHERE transaction_date < DATEADD(YEAR, -7, GETDATE())
    ORDER BY transaction_id;
 
OPEN cleanup_cursor;
FETCH NEXT FROM cleanup_cursor INTO @transaction_id;
 
WHILE @@FETCH_STATUS = 0
BEGIN
    -- Archive the transaction
    INSERT INTO transaction_archive SELECT * FROM transactions WHERE id = @transaction_id;
    DELETE FROM transactions WHERE id = @transaction_id;
    
    SET @processed = @processed + 1;
    
    -- Commit every batch_size rows
    IF @processed % @batch_size = 0
    BEGIN
        COMMIT;
        BEGIN TRANSACTION;
        PRINT 'Processed ' + CAST(@processed AS VARCHAR) + ' rows, committed.';
    END
    
    FETCH NEXT FROM cleanup_cursor INTO @transaction_id;
END
 
COMMIT;
CLOSE cleanup_cursor;
DEALLOCATE cleanup_cursor;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73

-- ALTERNATIVE 1: Window Functions
-- Eliminate cursors for running calculations
 
-- Running totals, moving averages, rankings - all set-based
SELECT 
    transaction_id,
    account_id,
    amount,
    -- Running total per account
    SUM(amount) OVER (PARTITION BY account_id 
                      ORDER BY transaction_date) AS running_balance,
    -- Previous transaction amount
    LAG(amount) OVER (PARTITION BY account_id 
                      ORDER BY transaction_date) AS prev_amount,
    -- Rank by amount within account
    RANK() OVER (PARTITION BY account_id 
                 ORDER BY amount DESC) AS amount_rank
FROM transactions;
 
-- ALTERNATIVE 2: MERGE Statement
-- Eliminate cursors for conditional insert/update
 
MERGE INTO target_table AS target
USING source_table AS source
ON target.id = source.id
WHEN MATCHED THEN
    UPDATE SET target.value = source.value, target.updated = GETDATE()
WHEN NOT MATCHED BY TARGET THEN
    INSERT (id, value, created) VALUES (source.id, source.value, GETDATE())
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;
 
-- ALTERNATIVE 3: Recursive CTEs
-- Eliminate cursors for hierarchical data traversal
 
WITH RECURSIVE org_hierarchy AS (
    -- Base case: top-level managers
    SELECT employee_id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Recursive case: subordinates
    SELECT e.employee_id, e.name, e.manager_id, h.level + 1
    FROM employees e
    INNER JOIN org_hierarchy h ON e.manager_id = h.employee_id
)
SELECT * FROM org_hierarchy ORDER BY level, name;
 
-- ALTERNATIVE 4: CROSS APPLY / LATERAL
-- Eliminate cursors for row-by-row function application
 
SELECT c.customer_id, c.name, recent.order_id, recent.order_date
FROM customers c
CROSS APPLY (
    SELECT TOP 3 order_id, order_date
    FROM orders o
    WHERE o.customer_id = c.customer_id
    ORDER BY order_date DESC
) AS recent;
 
-- ALTERNATIVE 5: Numbers Table / Tally Table
-- Eliminate cursors for generating sequences
 
-- Create or use existing numbers table
WITH numbers AS (
    SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
    FROM sys.objects a CROSS JOIN sys.objects b
)
SELECT DATEADD(DAY, n-1, '2024-01-01') AS calendar_date
FROM numbers
WHERE DATEADD(DAY, n-1, '2024-01-01') <= '2024-12-31';

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78

-- BEST PRACTICE TEMPLATE: Production-quality cursor implementation
 
DECLARE @error_count INT = 0;
DECLARE @success_count INT = 0;
DECLARE @max_rows INT = 100000;  -- Safety limit
 
-- Declare variables for ONLY the columns needed
DECLARE @order_id INT, @customer_email VARCHAR(200);
 
-- Use FAST_FORWARD for simple iteration
DECLARE order_cursor CURSOR LOCAL FAST_FORWARD FOR
    SELECT order_id, customer_email  -- Only needed columns
    FROM orders
    WHERE notification_sent = 0
    ORDER BY order_date;
 
BEGIN TRY
    OPEN order_cursor;
    FETCH NEXT FROM order_cursor INTO @order_id, @customer_email;
    
    WHILE @@FETCH_STATUS = 0 AND (@success_count + @error_count) < @max_rows
    BEGIN
        BEGIN TRY
            -- Tight processing block
            EXEC dbo.SendOrderConfirmation @order_id, @customer_email;
            
            UPDATE orders SET notification_sent = 1 WHERE order_id = @order_id;
            SET @success_count = @success_count + 1;
        END TRY
        BEGIN CATCH
            -- Log error but continue processing
            INSERT INTO error_log (order_id, error_message, error_time)
            VALUES (@order_id, ERROR_MESSAGE(), GETDATE());
            SET @error_count = @error_count + 1;
        END CATCH
        
        -- Periodic commit for large batches
        IF (@success_count + @error_count) % 1000 = 0
        BEGIN
            PRINT 'Progress: ' + CAST(@success_count AS VARCHAR) + ' sent, ' 
                  + CAST(@error_count AS VARCHAR) + ' failed';
        END
        
        FETCH NEXT FROM order_cursor INTO @order_id, @customer_email;
    END
END TRY
BEGIN CATCH
    -- Handle fatal errors
    PRINT 'Fatal error: ' + ERROR_MESSAGE();
END CATCH
 
-- GUARANTEED CLEANUP
IF CURSOR_STATUS('local', 'order_cursor') >= 0
    CLOSE order_cursor;
IF CURSOR_STATUS('local', 'order_cursor') >= -1
    DEALLOCATE order_cursor;
 
-- Report results
SELECT @success_count AS rows_sent, @error_count AS rows_failed;
 
-- ANTI-PATTERN: What to avoid
-- ❌ Nested cursors
DECLARE outer_cursor CURSOR FOR SELECT dept_id FROM departments;
OPEN outer_cursor;
FETCH NEXT FROM outer_cursor INTO @dept_id;
WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE inner_cursor CURSOR FOR  -- ❌ Cursor inside cursor!
        SELECT emp_id FROM employees WHERE department_id = @dept_id;
    -- This is O(n²) - use a JOIN instead!
END
 
-- ✓ CORRECT: Replace nested cursors with JOIN
DECLARE flat_cursor CURSOR FAST_FORWARD FOR
    SELECT d.dept_id, d.name, e.emp_id, e.salary
    FROM departments d
    INNER JOIN employees e ON d.dept_id = e.department_id
    ORDER BY d.dept_id, e.emp_id;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56

-- SQL Server: Monitor cursor usage
SELECT 
    c.name AS cursor_name,
    c.properties,
    c.creation_time,
    c.is_open,
    c.worker_time,  -- CPU time in microseconds
    c.reads,        -- Logical reads
    c.writes,       -- Logical writes
    c.dormant_duration  -- Time since last fetch
FROM sys.dm_exec_cursors(@@SPID) c;  -- Current session
 
-- All sessions' cursors (requires VIEW SERVER STATE)
SELECT 
    s.session_id,
    s.login_name,
    c.name,
    c.properties,
    c.creation_time,
    c.is_open
FROM sys.dm_exec_sessions s
CROSS APPLY sys.dm_exec_cursors(s.session_id) c
WHERE c.is_open = 1;
 
-- Oracle: Monitor cursor usage
SELECT 
    s.sid, s.serial#, s.username,
    oc.cursor_type, oc.sql_id,
    oc.child_address, oc.status
FROM v$open_cursor oc
JOIN v$session s ON oc.sid = s.sid
WHERE s.username IS NOT NULL;
 
-- Check for cursor-related wait states
SELECT session_id, wait_type, wait_time
FROM sys.dm_exec_requests
WHERE wait_type LIKE '%CURSOR%';
 
-- Performance baseline: Time individual operations
DECLARE @start_time DATETIME2, @open_time INT, @fetch_time INT, @close_time INT;
 
SET @start_time = SYSDATETIME();
OPEN my_cursor;
SET @open_time = DATEDIFF(MILLISECOND, @start_time, SYSDATETIME());
 
SET @start_time = SYSDATETIME();
-- Fetch loop here
SET @fetch_time = DATEDIFF(MILLISECOND, @start_time, SYSDATETIME());
 
SET @start_time = SYSDATETIME();
CLOSE my_cursor;
SET @close_time = DATEDIFF(MILLISECOND, @start_time, SYSDATETIME());
 
PRINT 'OPEN: ' + CAST(@open_time AS VARCHAR) + 'ms';
PRINT 'FETCH loop: ' + CAST(@fetch_time AS VARCHAR) + 'ms';
PRINT 'CLOSE: ' + CAST(@close_time AS VARCHAR) + 'ms';