Identity column emulation
Last updated
Last updated
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: )
Synapse Analytics:
Fabric Warehouse (generated by SQL Tran):
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.
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.
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.