Identity columns

Emulation context

In Synapse Analytics, an identity column automatically generates sequential numeric values based on a defined seed and increment. Identity columns are commonly used to generate surrogate primary keys. They are declared using the IDENTITY(seed, increment) property in a CREATE TABLE statement, and values are typically assigned automatically during INSERT.

For example:

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

Microsoft Fabric Warehouse does not support the IDENTITY property in table definitions. Fabric’s distributed architecture does not natively support database-managed auto-incremented values or automatic identity value generation. Identity logic must be implemented manually or externally during data loading. Microsoft’s documentation recommends alternatives such as using NEWID(), casting timestamps, or implementing custom sequence logic.

Due to this incompatibility, identity columns must be emulated or restructured when migrating from Synapse Analytics to Fabric Warehouse.

---

Emulation strategy

SQL Tran removes the IDENTITY property from table definitions and replaces automatic value generation with explicit logic. The emulation process includes:

• Replacing identity columns with standard INT or BIGINT columns marked NOT NULL.

• Adding a /* SQLTRAN FIX: IDENTITY(...) */ comment to annotate the removed identity property.

• Rewriting INSERT statements that omit the identity column to include explicitly generated values.

• Introducing a local variable (e.g., @maxIdSqlTran) that stores the current maximum value in the target table.

• Computing the next values manually using MAX(...) + N logic to emulate auto-increment behavior.

---

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'); */

Synapse Analytics:

CREATE TABLE #TempProducts (
    ProductID INT IDENTITY(1,1),
    ProductName NVARCHAR(100),
    Category NVARCHAR(50)
);

INSERT INTO #TempProducts (ProductName, Category)
SELECT Name, 
       CASE 
           WHEN Price > 100 THEN 'Furniture'
           ELSE 'Electronics'
       END AS Category
FROM Emulations.Items;

Fabric Warehouse (generated by SQL Tran):

CREATE TABLE #TempProducts (
    ProductID INT NOT NULL /* SQLTRAN FIX: IDENTITY(1,1) */,
    ProductName VARCHAR(200) /* SQLTRAN FIX: NVARCHAR(100) */,
    Category VARCHAR(100) /* SQLTRAN FIX: NVARCHAR(50) */
) WITH (DISTRIBUTION=ROUND_ROBIN);

SELECT @maxIdSqlTran = ISNULL(MAX(ProductID), 0) FROM #TempProducts; 
INSERT INTO #TempProducts ([ProductID] /* SQLTRAN FIX */, ProductName, Category)
SELECT @maxIdSqlTran + ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS [ProductID] /* SQLTRAN FIX */, Name, 
       CASE 
           WHEN Price > 100 THEN 'Furniture'
           ELSE 'Electronics'
       END AS Category
FROM Emulations.Items;

---

Important notes

• IDENTITY property removed: The IDENTITY(seed, increment) definition is stripped from the table and replaced with a standard column. A comment preserves the original declaration for reference.

• INSERT/VALUES emulation: For insert values, SQL Tran uses a MAX(...) + N pattern to emulate incremental identity values during insert.

• INSERT/SELECT emulation: For insert/select, SQL Tran uses a ROW_NUMBER() OVER pattern to emulate incremental identity values during insert.

• Seed and increment ignored: SQL Tran does not honor the original seed and increment values. All computed values increment by 1 regardless of the original identity definition. This may lead to incorrect sequences if a custom increment was specified.

• Explicit identity values preserved: When identity values are provided in source INSERT statements, SQL Tran does not modify the insert logic and preserves the original values.

• Unsupported commands passed through: Statements like SET IDENTITY_INSERT ON/OFF are passed through unchanged, even though they are not supported in Fabric and may result in runtime errors. These must be removed or restructured manually.

• Identity-returning functions not supported: Synapse functions such as SCOPE_IDENTITY() and @@IDENTITY are not supported in Fabric. SQL Tran passes them through without rewriting. A diagnostic comment is emitted for SCOPE_IDENTITY(), but @@IDENTITY is not flagged. Logic relying on these functions must be manually replaced using Fabric-compatible techniques.

• No sequence objects created: SQL Tran does not introduce SEQUENCE objects as a replacement for identity columns. If sequential ID generation is required via sequences, users must define and manage these manually.