Sequences
Emulation context
In Microsoft SQL Server, a sequence is a user-defined schema-bound object that generates a sequence of numeric values, typically used for surrogate keys or ordering operations. Sequences are created using CREATE SEQUENCE
and consumed with NEXT VALUE FOR
, allowing consistent numeric value generation across multiple tables or processes.
For example:
Microsoft Fabric Warehouse does not support sequence objects or the NEXT VALUE FOR
syntax. Fabric does not include a system catalog for sequences (sys.sequences
is unsupported), and there is no built-in mechanism to auto-generate numeric sequences. This limitation aligns with Fabric’s distributed execution model, which does not preserve row-by-row state across partitions.
Emulation strategy
SQL Tran emulates sequences by generating a combination of:
A backing table to store the sequence state.
A scalar function to retrieve the next value.
A stored procedure to update the sequence value after each use.
This emulation allows most sequence-based logic to be preserved across translations, even in environments like Fabric where sequences are not natively supported.
The emulation process includes:
Rewriting each
CREATE SEQUENCE
into:A table named after the original sequence (e.g.,
<SequenceName>
) with columns such ascurrent_value
,min_value
,max_value
,increment
, andcycle
.A function
GetNextFrom<SequenceName>(@step INT)
that simulatesNEXT VALUE FOR
.A procedure
Update<SequenceName>(@lastStep INT)
that updates thecurrent_value
accordingly.
Translating
NEXT VALUE FOR <SequenceName>
into calls toGetNextFrom<SequenceName>(...)
, using row offsets to simulate next-value generation for multi-row inserts.Inserting a call to the
Update<SequenceName>
procedure after value consumption.Commenting out
DEFAULT NEXT VALUE FOR ...
clauses in table definitions (these cannot be preserved in Fabric DDL).Avoiding the use of unsupported system views like
sys.sequences
.
This strategy ensures consistent, stateful, and isolated sequence logic while enabling deployment in Fabric Warehouse.
Code example
SQL Server:
Fabric Warehouse (generated by SQL Tran):
Important notes
No native support in Fabric: Fabric does not support sequence objects or the
NEXT VALUE FOR
syntax. Sequence metadata views likesys.sequences
are also unavailable.Emulated via T-SQL constructs: SQL Tran generates deterministic emulation logic using tables, functions, and procedures. This preserves value continuity and sequence behavior across inserts.
Sequence defaults in table definitions are commented out: When a table column defines
DEFAULT NEXT VALUE FOR ...
, SQL Tran comments out the expression and injects explicit logic in the corresponding insert statements instead.Multi-row inserts handled via row offset: SQL Tran computes the correct sequence values per row (e.g., using row number or step position) and advances the sequence after batch operations.
Isolation and safety maintained: Emulated sequences behave consistently even when used across different procedures or batches. However, concurrency control is not guaranteed unless additional locking or transaction logic is implemented manually.
Schema qualification recommended: In SQL Server, sequences are schema-bound and typically declared with an explicit schema (e.g.,
dbo.QueueNumberSequence
). Omitting the schema may result in invalid object names in the translated Fabric code.Manual review recommended: If the original sequence logic depends on caching, precision, or parallelism guarantees, users should review the generated logic to ensure it meets their expectations in a distributed Fabric environment.
Last updated