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:
CREATE SEQUENCE QueueNumberSequence
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 999
CYCLE;
INSERT INTO WaitingQueue (QueueNumber, CustomerName)
VALUES (NEXT VALUE FOR QueueNumberSequence, 'Alice');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 SEQUENCEinto: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_valueaccordingly.
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:
CREATE SEQUENCE dbo.OrderNumberSequence
START WITH 1000
INCREMENT BY 1;
CREATE TABLE dbo.Orders
(
OrderNumber INT PRIMARY KEY,
OrderName VARCHAR(100) NOT NULL,
Amount DECIMAL(18,2) NOT NULL
);
INSERT INTO dbo.Orders (OrderNumber, OrderName, Amount)
VALUES
(NEXT VALUE FOR dbo.OrderNumberSequence, 'Order A', 120.00),
(NEXT VALUE FOR dbo.OrderNumberSequence, 'Order B', 250.50),
(NEXT VALUE FOR dbo.OrderNumberSequence, 'Order C', 399.99);Fabric Warehouse (generated by SQL Tran):
CREATE TABLE dbo.OrderNumberSequence (
current_value INT, min_value INT, max_value INT, increment INT, should_cycle BIT
)
CREATE FUNCTION dbo.GetNextFromOrderNumberSequence(
@step INT
)
RETURNS INT
AS
BEGIN
DECLARE @start_value INT = 1000, @min_value INT = -2147483648, @max_value INT = 2147483647, @increment INT = 1, @should_cycle INT = 0;
DECLARE @value INT;
SELECT TOP (1) @value = current_value + (@increment * (@step - 1))
FROM
dbo.OrderNumberSequence;
IF @value IS NULL
SET @value = @start_value + (@increment * (@step - 1));
IF @value < @min_value
BEGIN
IF @should_cycle = 0
SET @value = NULL
ELSE
SET @value = @start_value - (@min_value - @value);
END;
IF @value > @max_value
BEGIN
IF @should_cycle = 0
SET @value = NULL
ELSE
SET @value = @start_value + @value - @max_value;
END;
RETURN @value
END;
CREATE PROCEDURE dbo.UpdateOrderNumberSequence(
@step INT
)
AS
BEGIN
DECLARE @start_value INT = 1000, @min_value INT = -2147483648, @max_value INT = 2147483647, @increment INT = 1, @should_cycle INT = 0;
IF NOT EXISTS (SELECT 1
FROM
dbo.OrderNumberSequence)
BEGIN
INSERT
INTO dbo.OrderNumberSequence (current_value, min_value, max_value, increment, should_cycle)
VALUES (@start_value, @min_value, @max_value, @increment, @should_cycle);
END;
DECLARE @current_value INT, @value INT;
SELECT TOP (1) @current_value = current_value
FROM
dbo.OrderNumberSequence;
SET @value = @current_value + (@increment * @step);
IF @value > @max_value OR (@value < @min_value)
BEGIN
IF @should_cycle = 0
BEGIN
THROW 5001, 'Sequence limit exceeded and cycling is disabled', 1;
END
ELSE
BEGIN
IF @value > @max_value
BEGIN
SET @value = @start_value + (@current_value + (@increment * (@step - 1))) - @max_value;
END;
IF @value < @min_value
BEGIN
SET @value = @start_value - (@min_value - (@current_value + (@increment * (@step - 1))));
END;
END;
END;
UPDATE dbo.OrderNumberSequence SET
current_value = @value;
END;
CREATE TABLE dbo.Orders
(
OrderNumber INT NOT NULL /* SQLTRAN FIX: PRIMARY KEY */,
OrderName VARCHAR(100) NOT NULL,
Amount DECIMAL(18,2) NOT NULL
);
GO
ALTER TABLE dbo.Orders ADD CONSTRAINT PK_dbo_Orders PRIMARY KEY NONCLUSTERED (OrderNumber) NOT ENFORCED;
INSERT INTO dbo.Orders (OrderNumber, OrderName, Amount)
VALUES
(dbo.GetNextFromOrderNumberSequence(1), 'Order A', 120.00),
(dbo.GetNextFromOrderNumberSequence(2), 'Order B', 250.50),
(dbo.GetNextFromOrderNumberSequence(3), 'Order C', 399.99);
EXEC dbo.UpdateOrderNumberSequence 3;
Important notes
No native support in Fabric: Fabric does not support sequence objects or the
NEXT VALUE FORsyntax. Sequence metadata views likesys.sequencesare 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