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 SEQUENCE into:

  • A table named after the original sequence (e.g., <SequenceName>) with columns such as current_value, min_value, max_value, increment, and cycle.

  • A function GetNextFrom<SequenceName>(@step INT) that simulates NEXT VALUE FOR.

  • A procedure Update<SequenceName>(@lastStep INT) that updates the current_value accordingly.

• Translating NEXT VALUE FOR <SequenceName> into calls to GetNextFrom<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;

Sequence emulation in SQL Tran

---

Important notes

• No native support in Fabric: Fabric does not support sequence objects or the NEXT VALUE FOR syntax. Sequence metadata views like sys.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.