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
, orFAST_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:
Fabric Warehouse (generated by SQL Tran):
Important notes
Unified translation pattern: Regardless of whether the source cursor is declared as
STATIC
,DYNAMIC
,KEYSET
,FAST_FORWARD
,FORWARD_ONLY
, orREAD_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. NoINSERT
,UPDATE
, orDELETE
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
andSCROLL
equivalence: Both forward-only and scrollable cursor patterns are flattened into a numeric loop over the materialized set. Explicit directional fetches (likeFETCH PRIOR
) are only supported where Fabric can deterministically process them.Update and concurrency semantics: Source cursor properties like
READ_ONLY
orFOR 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.
Last updated