Nested cursors
Emulation context
In SQL Server, nested cursors allow one cursor to operate inside the loop of another, enabling multi-level, row-by-row processing over related datasets. For example, an outer cursor might iterate over customers, while an inner cursor loops over each customer’s orders.
This pattern is often used in:
Parent-child data processing (e.g., customers and orders)
Hierarchical data traversal
Complex reporting with per-parent aggregations
However, Microsoft Fabric Warehouse lacks native cursor support and does not provide nested or stacked iterator constructs. Its distributed execution model focuses on set-based, parallelizable operations rather than nested procedural loops, making direct translation of nested cursors particularly challenging.
SQL Tran must transform these nested patterns into a compatible, deterministic structure using static materialization and iterative control flow.
Emulation strategy
SQL Tran emulates nested cursor patterns using layered translation:
Each cursor (outer and inner) is translated into its own pre-materialized temporary table (e.g.,
#outer_cursor
,#inner_cursor
) with an explicit row number (ROW_NUMBER()
), snapshotting the data at the time of translation.The outer cursor loop drives the top-level iteration using an index variable (e.g.,
@CurrentRow_outer_cursor
), while the inner cursor loop operates inside, using its own independent index (e.g.,@CurrentRow_inner_cursor
).The temp tables are created and dropped at their respective scopes.
Data dependencies between outer and inner loops (such as passing a parent key from the outer cursor into the inner cursor’s
WHERE
clause) are preserved by translating them into parameterized subqueries feeding the inner temp table.
Key transformation behaviors include:
Scope isolation: Each temp table and loop variable is maintained separately to avoid cross-interference.
Repeated materialization: The inner temp table is re-created for each outer loop iteration, matching the per-parent filtering from the original SQL Server pattern.
Sequential control: Although Fabric is optimized for set-based operations, SQL Tran preserves the nested loop logic deterministically, ensuring row-by-row consistency.
This approach makes nested cursor emulation feasible — but introduces performance and resource considerations, as materializing and iterating over large inner datasets repeatedly can be expensive.
Code example
SQL Server:
Fabric Warehouse (generated by SQL Tran):
Important notes
Temp table layering: Each nested cursor introduces an additional temp table (
#outer_cursor
,#inner_cursor
), increasing schema complexity and execution overhead.Repeated table creation: The inner cursor’s temp table is created and dropped on every outer iteration. While functionally correct, this can lead to performance bottlenecks, especially if the inner dataset is large.
Variable isolation: Outer and inner cursor loop variables (e.g.,
@CurrentRow_outer_cursor
and@CurrentRow_inner_cursor
) are isolated but follow consistent naming patterns. Manual review is advised to ensure no accidental cross-scope contamination.No parallelization: While Fabric is a distributed platform, the translated nested loops execute serially, reflecting the original SQL Server procedural logic. This may negate some of Fabric’s set-based performance advantages.
Manual review recommended: Nested cursor translations are structurally correct but may not be optimal for performance. Users should assess whether the logic can be rewritten as set-based queries or analytic patterns in Fabric for better scalability.
Last updated