SQL Tran Docs
  • Overview
    • About
    • Getting started
    • Object lifecycle
    • Why is there no AI inside?
    • Performance considerations
    • Security considerations
    • Assessment
    • Unlocking projects
    • Interface walk-through
    • Translation scenarios
    • Prerequisites for Azure Marketplace deployment
  • Emulation scenarios
    • Emulation in SQL Tran
    • SQL Server to Fabric Warehouse
      • Data types
      • Case sensitivity
      • Cursors
        • Basic cursor loop
        • Cursor types
        • Fetch direction modes
        • Cursors in control flow
        • Nested cursors
        • Data modification cursors
        • Multiple cursors
        • Subqueries and filtering
      • Named procedure parameters
      • Result set limiting
      • MERGE statements
      • Computed columns
      • Temporary tables
      • External tables
      • Identity columns
      • Default values
      • Sequences
      • Unsupported system features
    • Synapse Analytics to Fabric Warehouse
      • Data types
      • Case sensitivity
      • Named procedure parameters
      • Result set limiting
      • MERGE statements
      • Temporary tables
      • External tables
      • Identity columns
      • Default values
      • Materialized views
      • Create table as select (CTAS)
      • Unsupported system features
    • SQL Server to Synapse Analytics
    • Oracle to PostgreSQL
  • Project wizard
    • Source database
    • Target database
    • Wrapping up
  • Projects
    • Project list
    • Overview
    • Workspace
    • Reports
    • Tests
    • Scratch pad
    • Settings
      • Project name
      • Mapping
      • Database connections
    • Navigation
    • Object complexity
    • Static analysis
    • Translation errors
    • Exporting and importing projects
  • Workspace
    • Object tree
    • Data lineage
    • Code
    • Actions
      • Overriding source
      • Overriding target
      • Ignoring objects
  • Tests
    • Workflow
    • Configure SQL Tran
    • Connecting to databases
      • Fabric Warehouse
      • Synapse Dedicated SQL Pool
      • Azure SQL Database, Azure SQL Managed Instance, Microsoft SQL Server
    • Tables
    • Views
    • Procedures
    • Functions
    • Triggers
    • Performance tests
  • Scripter
    • About
    • Supported databases
    • SQL Server
    • Azure SQL
    • Synapse Dedicated Pool
    • Oracle
    • PostgreSQL
    • MySQL
  • Microsoft training - SQL Tran
    • Introduction
    • Licensing plans
    • Deploying and activating
    • Creating a new project
    • Exploring the Overview screen
    • Inside the Workspace screen
    • Code translation and emulations
      • Data type emulation
      • Case sensitivity emulation
      • Named parameters emulation
      • Result set limiting emulation
      • Merge statements emulation
      • Identity column emulation
      • Default values emulation
      • Detection of unsupported features
Powered by GitBook
On this page
  • Code example
  • Training video example
  1. Microsoft training - SQL Tran
  2. Code translation and emulations

Identity column emulation

PreviousMerge statements emulationNextDefault values emulation

Last updated 9 hours ago

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: )


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

Training video example

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.

Identity columns
Identity column removed in target table definition
Identity emulation in INSERT SELECT procedure
Identity emulation in INSERT VALUES procedure