Identity column emulation

In Synapse Analytics, identity columns automatically generate sequential values, commonly used for primary keys and surrogate identifiers.

This functionality is not supported in Microsoft Fabric Warehouse due to its distributed architecture, which lacks native auto-increment capabilities.

Without identity support, migrating code that relies on automatic value generation becomes complex and error-prone if handled manually.

SQL Tran addresses this by emulating identity behavior: it removes the unsupported identity property from table definitions and rewrites the related INSERT statements to generate and insert sequential values manually.

This preserves the original application logic and avoids costly refactoring, enabling seamless migrations even for systems heavily dependent on identity columns.

(For more information, see the following emulation reference page: Identity columns)

---

Code example

Synapse Analytics:

CREATE TABLE Customers ( 
    CustomerID INT IDENTITY(1,1),
    Name NVARCHAR(100)
);

INSERT INTO Customers (Name)
VALUES ('Alice'), ('Bob');

Fabric Warehouse (generated by SQL Tran):

DECLARE @maxIdSqlTran INT;
CREATE TABLE Customers ( 
    CustomerID INT NOT NULL /* SQLTRAN FIX: IDENTITY(1,1) */,
    Name VARCHAR(200) /* SQLTRAN FIX: NVARCHAR(100) */
);

SELECT @maxIdSqlTran = ISNULL(MAX(CustomerID), 0)
FROM
	Customers;
INSERT
	INTO Customers ([CustomerID], Name)
	VALUES (@maxIdSqlTran + 1, 'Alice'),
	(@maxIdSqlTran + 2, 'Bob');
 /* SQLTRAN FIX: INSERT INTO Customers (Name)
VALUES ('Alice'), ('Bob'); */

---

Training video example

In the example shown, SQL Tran processes a table definition with an identity column.

Since Fabric does not support the identity property, SQL Tran comments out the identity attribute in the target SQL, annotating it for visibility.

Identity column removed in target table definition

The procedure that inserts data into this table originally relies on the identity column for automatic ID generation.

In the source SQL, the ErrorLogID is omitted from the INSERT INTO ... SELECT ... statement, as identity values are handled automatically.

In the target SQL, SQL Tran emulates this behavior by introducing logic to retrieve the current maximum ErrorLogID and calculate the next value manually.

This ensures the procedure behaves exactly as it did in Synapse, even without native identity support in Fabric.

Identity emulation in INSERT SELECT procedure

Another procedure demonstrates a similar emulation, but this time the original INSERT INTO ... VALUES ... syntax is used.

SQL Tran adapts the code to ensure the identity behavior is preserved, adjusting the logic as necessary depending on the original statement form.

Identity emulation in INSERT VALUES procedure