Identity columns
Emulation context
In Microsoft SQL Server, 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) PRIMARY KEY,
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 SQL Server 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
orBIGINT
columns markedNOT 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
SQL Server:
CREATE TABLE Customers (
CustomerID INT IDENTITY(1,1) PRIMARY KEY,
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) */ /* SQLTRAN FIX: PRIMARY KEY */,
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'); */
SQL Server:
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: TheIDENTITY(seed, increment)
definition is stripped from the table and replaced with a standard column. A comment preserves the original declaration for reference.Manual value generation injected: SQL Tran uses a
MAX(...) + N
pattern to emulate incremental identity values during insert. This preserves uniqueness but does not enforce database-side auto-incrementation.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.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: SQL Server functions such as
SCOPE_IDENTITY()
and@@IDENTITY
are not supported in Fabric. SQL Tran passes them through without rewriting. A diagnostic comment is emitted forSCOPE_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
Last updated