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
INTorBIGINTcolumns markedNOT NULL.Adding a
/* SQLTRAN FIX: IDENTITY(...) */comment to annotate the removed identity property.Rewriting
INSERTstatements 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(...) + Nlogic 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
IDENTITYproperty removed: TheIDENTITY(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(...) + Npattern to emulate incremental identity values during insert.INSERT/SELECT emulation: For insert/select, SQL Tran uses a
ROW_NUMBER() OVERpattern 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
INSERTstatements, SQL Tran does not modify the insert logic and preserves the original values.Unsupported commands passed through: Statements like
SET IDENTITY_INSERT ON/OFFare 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@@IDENTITYare not supported in Fabric. SQL Tran passes them through without rewriting. A diagnostic comment is emitted forSCOPE_IDENTITY(), but@@IDENTITYis not flagged. Logic relying on these functions must be manually replaced using Fabric-compatible techniques.No sequence objects created: SQL Tran does not introduce
SEQUENCEobjects as a replacement for identity columns. If sequential ID generation is required via sequences, users must define and manage these manually.
Last updated