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:
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:
Fabric Warehouse (generated by SQL Tran):
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.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: 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 manage these manually.Manual review required: Emulated identity logic should be reviewed to ensure it maintains expected sequencing, especially when custom seeds, increments, or pre-existing values are involved. Alternative strategies using Fabric-compatible mechanisms may be needed for large-scale ingestion.
Last updated