Fabric Warehouse looks just like SQL Server. It uses T-SQL, has schemas, stored procs and more. Yet, you migrate your first proper workload and see “that’s not supported” errors you never had to think about before.

Fabric Warehouse isn’t SQL Server. Under the bonnet it’s storing everything as Delta Parquet in OneLake, and that storage format shapes what T-SQL can and can’t do. Here’s what actually trips people up, with the SQL Server way and the Fabric way side by side.

Surrogate keys: no more IDENTITY

SQL Server:

CREATE TABLE dbo.DimCustomer (

    CustomerKey INT IDENTITY(1,1) PRIMARY KEY,

    CustomerName VARCHAR(100)

);

Fabric: IDENTITY isn’t supported on Fabric tables. If you’re building a star schema and need surrogate keys, you’ll need to generate them yourself: ROW_NUMBER() over a staging load or a separate key-management table you maintain in your ETL.

SELECT

    ROW_NUMBER() OVER (ORDER BY CustomerName) AS CustomerKey,

    CustomerName

INTO dbo.DimCustomer

FROM staging.Customer;

Primary keys and foreign keys don’t actually enforce anything

SQL Server: a primary key or foreign key constraint is enforced. Try to insert a duplicate row and the engine stops you.

Fabric: PRIMARY KEY and UNIQUE are only allowed as NONCLUSTERED and NOT ENFORCED. Same for FOREIGN KEY, it must be NOT ENFORCED. These constraints exist purely as metadata, so that tools like Power BI can infer relationships. Your ETL is now the only thing standing between you and duplicate keys, so data quality checks need to be manually added into your pipeline rather than relying on the database to catch mistakes.

ALTER TABLE dbo.DimCustomer

ADD CONSTRAINT PK_DimCustomer PRIMARY KEY NONCLUSTERED (CustomerKey) NOT ENFORCED;

ALTER TABLE but not columns

SQL Server: alter a column’s type, add a computed column, add a check constraint, more or less whatever you like.

Fabric: you can add nullable columns, drop columns, and add or drop unenforced PK/UK/FK constraints. Altering an existing column is not supported, if you need to change a column type, the usual workaround is to create a new column, backfill it, drop the old one, rename the new one with sp_rename.

Data types: the list is shorter, and it’s shorter for a reason

Fabric stores tables as Delta Parquet, so any type without a clean Parquet equivalent is not available. The common ones that catch people out:

SQL Server Fabric alternative
datetime, smalldatetime datetime2
datetimeoffset datetime2 (cast to datetimeoffset at query time with AT TIME ZONE if you need it)
money, smallmoney decimal (you lose the currency symbol formatting)
nchar, nvarchar char, varchar (Fabric warehouses are UTF-8 by default, so you don’t need the n prefix for unicode)
text, ntext varchar
image varbinary
tinyint smallint
xml there’s no equivalent
geography, geometry store as lat/long columns, or varchar well-known-text, or varbinary well-known-binary

 

Also worth knowing: varchar(max) and varbinary(max) cap out at 16 MB in Fabric, and datetime2/time precision is limited to 6 digits.

Things you’ll reach for that just aren’t there

A handful of statements that are second nature in SQL Server simply don’t work in Fabric Warehouse. Don’t try them, even if they appear to run without error, Microsoft’s own docs warn they can leave your warehouse in a bad state:

  • Recursive CTEs. Standard, sequential, and nested CTEs all work, but WITH cte AS (… UNION ALL SELECT … FROM cte) recursion doesn’t. Hierarchies need a different approach, often handled in a Spark notebook instead.
  • SELECT … FOR XML and FOR JSON used as a subquery. FOR JSON only works as the outermost operator in a query, not embedded inside one.
  • SET ROWCOUNT is unsupported.
  • Materialized views, indexed views, computed columns, sparse columns, unique indexes, partitioned tables. None of these exist in Fabric. Performance optimisation leans heavily on how you structure and distribute your tables.
  • BULK LOAD and CREATE USER. Loading is done through the Fabric ingestion tools (pipelines, dataflows, COPY INTO) rather than BULK INSERT, and user management runs through Fabric workspace roles rather than T-SQL logins.
  • sp_showspaceused. Use Fabric’s own capacity and storage metrics instead.

MERGE and TRUNCATE: good news, these just work

They weren’t available at release, but in the past year they arrived. MERGE is fully supported and generally available, TRUNCATE TABLE works too. And session-scoped #temp tables work much like they do in SQL Server, though you get a choice between a standard MDF-backed temp table and a distributed, Parquet-backed one if you want it to behave more like a permanent table.

Naming rules you’ll only discover by hitting them

Schema and table names can’t contain a / or \, and can’t end with a “.”. This seems trivial, until an automated script generates a name that includes one and fails halfway through a batch of 200 tables.

The practical takeaway

None of this makes Fabric Warehouse a lesser product, it’s just built for a different purpose. It’s an analytical store on open Parquet files, not a general-purpose transactional engine, so a lot of what’s missing is stuff that belongs in OLTP systems anyway. The pain is almost entirely in migration, so allow time to mitigate for these differences as that’s where most migrations lose the most time.

 

Tags: , , , ,