Edit

Data types in Microsoft Fabric

  • Article

Applies to: SQL analytics endpoint and Warehouse in Microsoft Fabric

Tables in Microsoft Fabric support the most commonly used T-SQL data types.

  • For more information on table creation, see Tables.

Data types in Warehouse

Warehouse supports a subset of T-SQL data types:

Category Supported data types
Exact numerics
  • bit
  • bigint
  • int
  • smallint
  • decimal
  • numeric
Approximate numerics
  • float
  • real
Date and time
  • date
  • datetime2
  • time
Character strings
  • char
  • varchar
Binary strings
  • varbinary
  • uniqueidentifer

Note

The precision for datetime2 and time is limited to 6 digits of precision on fractions of seconds.

The uniqueidentifier data type is a T-SQL data type, without a matching data type in Delta Parquet. As a result, it's stored as a binary type. Warehouse supports storing and reading uniqueidentifier columns, but these values can't be read on the SQL analytics endpoint. Reading uniqueidentifier values in the lakehouse displays a binary representation of the original values. As a result, features such as cross-joins between Warehouse and SQL analytics endpoint using a uniqueidentifier column doesn't work as expected.

For more information about the supported data types including their precisions, see data types in CREATE TABLE reference.

Unsupported data types

For T-SQL data types that aren't currently supported, some alternatives are available. Make sure you evaluate the use of these types, as precision and query behavior vary:

Unsupported data type Alternatives available
money and smallmoney Use decimal, however note that it can't store the monetary unit.
datetime and smalldatetime Use datetime2.
nchar and nvarchar Use char and varchar respectively, as there's no similar unicode data type in Parquet. The char and varchar types in a UTF-8 collation might use more storage than nchar and nvarchar to store unicode data. To understand the impact on your environment, see Storage differences between UTF-8 and UTF-16.
text and ntext Use varchar.
image Use varbinary.

Unsupported data types can still be used in T-SQL code for variables, or any in-memory use in session. Creating tables or views that persist data on disk with any of these types isn't allowed.

For a guide to create a table in Warehouse, see Create tables.

Autogenerated data types in the SQL analytics endpoint

The tables in SQL analytics endpoint are automatically created whenever a table is created in the associated lakehouse. The column types in the SQL analytics endpoint tables are derived from the source Delta types.

The rules for mapping original Delta types to the SQL types in SQL analytics endpoint are shown in the following table:

Delta data type SQL data type (mapped)
LONG, BIGINT bigint
BOOLEAN, BOOL bit
INT, INTEGER int
TINYINT, BYTE, SMALLINT, SHORT smallint
DOUBLE float
FLOAT, REAL real
DATE date
TIMESTAMP datetime2
CHAR(n) varchar(n) with Latin1_General_100_BIN2_UTF8 collation
STRING, VARCHAR(n) varchar(n) with Latin1_General_100_BIN2_UTF8 collation
STRING, VARCHAR(MAX) varchar(8000) with Latin1_General_100_BIN2_UTF8 collation
BINARY varbinary(n)
DECIMAL, DEC, NUMERIC decimal(p,s)

The columns that have the types that aren't listed in the table aren't represented as the table columns in the SQL analytics endpoint.

Related content