Data types in Microsoft Fabric
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.
- For syntax, see CREATE TABLE
Data types in Warehouse
Warehouse supports a subset of T-SQL data types. Each offered data type is based on the SQL Server data type of the same name. For more information, to the reference article for each in the following table.
| Category | Supported data types |
|---|---|
| Exact numerics | |
| Approximate numerics | |
| Date and time | |
| Fixed-length character strings | |
| Variable length character strings |
|
| Binary strings |
* 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 don't work as expected.
*** Support for varchar (max) and varbinary (max) is currently in preview.
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. |
| datetimeoffset | Use datetime2, however you can use datetimeoffset for converting data with CAST the AT TIME ZONE (Transact-SQL) function. For an example, see datetimeoffset. |
| 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. |
| tinyint | Use smallint. |
| geography | No equivalent. |
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(MAX) 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.