Edit

T-SQL surface area in Fabric Data Warehouse

Applies to: ✅ SQL analytics endpoint and Warehouse in Microsoft Fabric

This article covers the T-SQL language syntax capabilities of Microsoft Fabric, when querying the SQL analytics endpoint or Warehouse.

For SQL database in Fabric, see Limitations in SQL database (preview).

Note

For more information on upcoming feature development for Fabric Data Warehouse, see the Fabric Data Warehouse release plan.

T-SQL surface area

Fabric Data Warehouse supports T-SQL tables, views, stored procedures, functions, permissions, and security roles.

  • For more information about CREATE and DROP TABLE support in Warehouse, see Tables.
  • For more information about supported data types in Warehouse, see Data types.
  • You can also create T-SQL views, functions, and procedures on top of the tables that reference your Delta Lake data in the SQL analytics endpoint of the Lakehouse.
    • Creating, altering, and dropping tables, and insert, update, and delete operations are only supported in Warehouse in Microsoft Fabric, not in the SQL analytics endpoint of the Lakehouse.
  • Fabric Warehouse and SQL analytics endpoint both support standard, sequential, and nested common table expressions (CTEs). While CTEs are generally available in Microsoft Fabric, nested CTEs are currently a preview feature. For more information, see Nested Common Table Expression (CTE) in Fabric data warehousing (Transact-SQL).
  • TRUNCATE TABLE is supported in Warehouse.
  • To change the name of the column in a user table in Warehouse, use the sp_rename stored procedure.
  • A subset of query and join hints are supported in Warehouse. For more information, see Hints (Transact-SQL).
  • Session-scoped #temp tables are supported in Warehouse in Microsoft Fabric. For more information, see Temp tables in Fabric Data Warehouse.
  • Currently, only the following subset of ALTER TABLE operations in Warehouse in Microsoft Fabric are supported:
    • You can ADD nullable columns of supported column data types.
    • DROP COLUMN is supported.
    • ADD or DROP PRIMARY KEY, UNIQUE, and FOREIGN KEY column constraints are supported, but only if the NOT ENFORCED option is specified. All other ALTER TABLE operations are blocked. There are limitations with adding table constraints or columns when using Source Control with Warehouse.
    • ALTER TABLE on distributed temporary tables is supported.
    • ALTER TABLE ... ALTER COLUMN is in preview. For more information, see ALTER COLUMN in Fabric Data Warehouse.
    • You can execute supported ALTER TABLE statements inside an explicit user-defined transaction in Warehouse in Microsoft Fabric.
  • MERGE syntax is supported and is a generally available feature. For more information, see MERGE T-SQL syntax.
  • While Fabric Data Warehouse supports many AI functions (preview) to enable advanced text processing without leaving your warehouse, the vector data type isn't supported.

Limitations

Currently, the following commands aren't supported. Don't try to use these commands. Even though they might appear to succeed, they could cause problems for your warehouse.

  • BULK LOAD, though bcp is supported as a preview feature.
  • CREATE USER
  • FOR JSON must be the last operator in the query, so you can't use it inside subqueries.
  • Manually created multi-column stats
  • Materialized views
  • PREDICT
  • Queries targeting system and user tables
  • Recursive queries
  • Schema and table names can't contain / or \
  • SELECT - FOR XML
  • SET ROWCOUNT
  • SET TRANSACTION ISOLATION LEVEL
  • sp_showspaceused
  • Synonyms
  • Triggers
  • Vector data type and search functions

Related content

  • Last updated on