Data types

Emulation context

Azure Synapse Analytics (dedicated SQL pools) supports a wide range of SQL Server-compatible data types, including Unicode types like NVARCHAR, legacy types such as NTEXT, MONEY, and TEXT, and extended types such as DATETIMEOFFSET. These types offer compatibility for traditional SQL Server workloads and flexibility in ETL pipelines.

However, Microsoft Fabric Warehouse supports a reduced and simplified type system, optimized for distributed analytics and Parquet-backed columnar storage. Several Synapse data types are either unsupported or behave differently in Fabric.

For example:

• NVARCHAR is not supported. Fabric uses VARCHAR with UTF-8 encoding for Unicode data.

• NTEXT, TEXT, and MONEY are not supported at all.

• DATETIMEOFFSET is replaced by DATETIME2, which removes time zone information.

These incompatibilities may result in schema deployment failures or runtime behavior differences unless addressed through automated translation or manual refactoring.

---

Emulation strategy

SQL Tran automatically converts Synapse data types to their nearest Fabric-compatible equivalents to ensure successful translation and deployment. The goal is to preserve data semantics where possible while enforcing compatibility with Fabric’s SQL surface.

Typical translation behaviors include:

• NVARCHAR(n) → VARCHAR(n * 2) — SQL Tran doubles the length to preserve UTF-8 compatibility.

• NCHAR(n) → CHAR(n * 2) — similarly expanded for Unicode content.

• NTEXT, TEXT → VARCHAR(MAX) — legacy text types are converted to UTF-8 encoded strings.

• MONEY → DECIMAL(19,4) — fixed precision is applied to preserve currency semantics.

• TINYINT → SMALLINT — due to lack of native support in Fabric.

• DATETIMEOFFSET → DATETIME2(6) — time zone offset data is removed.

• DATETIME → DATETIME2(6) — precision is increased for consistency across datetime usage.

• SMALLDATETIME → DATETIME2(6) — promoted to preserve time detail.

• BINARY(n) → VARBINARY(n) — fixed binary types are rewritten to variable-length.

The following types are retained without modification:

• INT, BIGINT, SMALLINT

• FLOAT, REAL, DECIMAL, NUMERIC

• BIT, DATE, TIME(n), DATETIME2(n)

• VARBINARY(n), UNIQUEIDENTIFIER

• VARCHAR(n), CHAR(n)

Expressions used within SELECT statements (e.g., CAST(...), CONVERT(...), UPPER(...)) are retained without modification.

---

Code example

Synapse Analytics:

CREATE TABLE SampleDataTypes (
    ID INT NOT NULL,
    Name NVARCHAR(100),
    Description NTEXT,
    CreatedAt DATETIMEOFFSET,
    RegisteredAt DATETIME,
    BirthDate DATE,
    LastLogin DATETIME2,
    Score FLOAT,
    Balance MONEY,
    IsActive BIT,
    Document VARBINARY(MAX),
    UserID UNIQUEIDENTIFIER,
    PriorityLevel TINYINT
);

Fabric Warehouse (generated by SQL Tran):

CREATE TABLE SampleDataTypes (
    ID INT NOT NULL,
    Name VARCHAR(200) /* SQLTRAN FIX: NVARCHAR(100) */,
    Description VARCHAR(MAX) /* SQLTRAN FIX: NTEXT */,
    CreatedAt DATETIME2(6) /* SQLTRAN FIX: DATETIMEOFFSET */,
    RegisteredAt DATETIME2(6) /* SQLTRAN FIX: DATETIME */,
    BirthDate DATE,
    LastLogin DATETIME2(6) /* SQLTRAN FIX: DATETIME2 */,
    Score FLOAT,
    Balance DECIMAL(19,4) /* SQLTRAN FIX: MONEY */,
    IsActive BIT,
    Document VARBINARY(MAX),
    UserID UNIQUEIDENTIFIER,
    PriorityLevel SMALLINT /* SQLTRAN FIX: TINYINT */
);

Data types emulation in SQL Tran

---

Important notes

• UTF-8 collation and size expansion: Fabric assumes UTF-8 encoding for all VARCHAR and CHAR columns. SQL Tran compensates for multibyte characters by doubling the declared length of NVARCHAR and NCHAR types to avoid truncation. Users should validate these conversions based on real-world data sizes.

• Unsupported types: Unsupported types are either rewritten or rejected during translation. NTEXT, TEXT, and MONEY are rewritten to compatible Fabric types. TINYINT is not supported in Fabric and is replaced with SMALLINT.

• Temporal conversions: During conversion, DATETIMEOFFSET loses its time zone information, as Fabric does not support time zone–aware types. Both SMALLDATETIME and DATETIME are promoted to DATETIME2(6) to ensure consistent precision and compatibility.

• Binary handling: BINARY(n) columns are rewritten as VARBINARY(n) to align with Fabric’s supported binary types.

• Expression preservation: SQL Tran does not alter inline expressions using T-SQL functions such as CAST, CONVERT, or UPPER.

• Manual review recommended: For full compatibility, developers should review any columns that use unsupported types. Time zone–dependent logic based on DATETIMEOFFSET may require revision, as Fabric does not support time zone–aware datatypes. Currency formatting or rounding logic based on the MONEY type should be verified for precision consistency.