Cursor types

Emulation context

SQL Server supports multiple cursor types — including STATIC, DYNAMIC, KEYSET, FAST_FORWARD, FORWARD_ONLY, and READ_ONLY — each providing different behaviors related to scrollability, sensitivity to underlying data changes, and update capabilities.

For example:

• STATIC cursors work on a fixed snapshot of the result set.
• DYNAMIC cursors reflect live inserts, updates, and deletes during iteration.
• KEYSET cursors provide hybrid visibility for keys but not data changes.
• FAST_FORWARD optimizes for forward-only, read-only traversal.

However, Microsoft Fabric Warehouse does not support cursors natively, nor does it have equivalents for these behaviors. Its distributed, stateless architecture is designed around set-based operations and does not maintain session-local row pointers or live-sensitive iterators.

This requires SQL Tran to flatten all cursor types into a uniform, Fabric-compatible pattern that can execute reliably without relying on server-side cursor mechanics.

Emulation strategy

SQL Tran emulates cursor types using a unified translation approach:

• It materializes the result set into a temporary table (#person_cursor) with an explicit row number (ROW_NUMBER()), effectively snapshotting the data at the time of execution.
• It replaces directional cursor operations with a deterministic loop (WHILE) that increments a scalar index variable (e.g., @CurrentRow_person_cursor).
• Regardless of whether the original cursor was STATIC, DYNAMIC, KEYSET, or FAST_FORWARD, the translated Fabric code uses the same pre-materialized table and numeric loop logic.
• Cursor-specific features such as live data sensitivity, keyset tracking, or forward-only optimizations are not preserved.

This approach ensures that the code executes reliably in Fabric, but at the cost of cursor-type semantics, which must be manually reviewed if critical to business logic.

Code example

SQL Server:

CREATE PROCEDURE dbo.ProcessPeople
AS
BEGIN
    DECLARE @BusinessEntityID INT, @LastName NVARCHAR(50);
    DECLARE person_cursor CURSOR STATIC FOR
    SELECT BusinessEntityID, LastName FROM Person.Person;
    OPEN person_cursor;
    FETCH NEXT FROM person_cursor INTO @BusinessEntityID, @LastName;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Placeholder for logic
        FETCH NEXT FROM person_cursor INTO @BusinessEntityID, @LastName;
    END;
    CLOSE person_cursor;
    DEALLOCATE person_cursor;
END

Fabric Warehouse (generated by SQL Tran):

CREATE PROCEDURE dbo.ProcessPeople
AS
BEGIN
    DECLARE @BusinessEntityID INT, @LastName NVARCHAR(50);
    DECLARE @RowCount_person_cursor INT;
DECLARE @CurrentRow_person_cursor INT = 1;
CREATE TABLE #person_cursor (
	RowNum INT, BusinessEntityID INT, LastName NVARCHAR(50)
)INSERT
	INTO #person_cursor (RowNum, BusinessEntityID, LastName)
	SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum,
		BusinessEntityID,
		LastName
	FROM
		Person.Person;
SELECT @RowCount_person_cursor = COUNT(*)
FROM
	#person_cursor;
    
    SELECT @BusinessEntityID = BusinessEntityID,
	@LastName = LastName
FROM
	#person_cursor
WHERE
	RowNum = @CurrentRow_person_cursor;
    WHILE @CurrentRow_person_cursor <= @RowCount_person_cursor
    BEGIN
        -- Placeholder for logic
        SET @CurrentRow_person_cursor = @CurrentRow_person_cursor + 1;
SELECT @BusinessEntityID = BusinessEntityID,
	@LastName = LastName
FROM
	#person_cursor
WHERE
	RowNum = @CurrentRow_person_cursor;
    END;
    
    DROP TABLE IF EXISTS #person_cursor;
END

Important notes

• Unified translation pattern: Regardless of whether the source cursor is declared as STATIC, DYNAMIC, KEYSET, FAST_FORWARD, FORWARD_ONLY, or READ_ONLY, SQL Tran rewrites the logic into the same emulation structure: a pre-materialized temporary table and a deterministic numeric loop. The cursor type is not preserved in the translated Fabric code.
• Loss of live sensitivity: Cursor types that in SQL Server would reflect live changes (such as DYNAMIC) are flattened into a static snapshot. The Fabric translation works only on the data as captured at the time of temp table creation. No INSERT, UPDATE, or DELETE operations performed during loop execution are visible in the iteration.
• No diagnostic warnings added: The translated output does not include comments, warnings, or annotations about dropped or ignored cursor-type behaviors. Users must manually assess whether the original logic relied on type-specific semantics.
• FORWARD_ONLY and SCROLL equivalence: Both forward-only and scrollable cursor patterns are flattened into a numeric loop over the materialized set. Explicit directional fetches (like FETCH PRIOR) are only supported where Fabric can deterministically process them.
• Update and concurrency semantics: Source cursor properties like READ_ONLY or FOR UPDATE are preserved syntactically but have no operational effect. Any required update or concurrency control must be reimplemented in Fabric-compatible logic.
• Manual review advised: Code that critically depends on dynamic updates, keyset sensitivity, or scrollable navigation should be manually reviewed, as the translation behaves purely as a static, forward-iterating snapshot.