Data types for tables in SQL Data Warehouse

• Overview
• Data Types
• Distribute
• Index
• Partition
• Statistics
• Temporary

SQL Data Warehouse supports the most commonly used data types. Below is a list of the data types supported by SQL Data Warehouse. For additional details on data type support, see create table.

Supported Data Types
bigint	decimal	smallint
binary	float	smallmoney
bit	int	sysname
char	money	time
date	nchar	tinyint
datetime	nvarchar	uniqueidentifier
datetime2	real	varbinary
datetimeoffset	smalldatetime	varchar

Data type best practices

When defining your column types, using the smallest data type which will support your data will improve query performance. This is especially important for CHAR and VARCHAR columns. If the longest value in a column is 25 characters, then define your column as VARCHAR(25). Avoid defining all character columns to a large default length. In addition, define columns as VARCHAR when that is all that is needed rather than use NVARCHAR. Use NVARCHAR(4000) or VARCHAR(8000) when possible instead of NVARCHAR(MAX) or VARCHAR(MAX).

Polybase limitation

If you are using Polybase to load your tables, define your tables so that the maximum possible row size, including the full length of variable length columns, does not exceed 32,767 bytes. While you can define a row with variable length data that can exceed this width and load rows with BCP, you will not be able to use Polybase to load this data. Polybase support for wide rows will be added soon.

Unsupported data types

If you are migrating your database from another SQL platform like Azure SQL Database, as you migrate, you may encounter some data types that are not supported on SQL Data Warehouse. Below are unsupported data types as well as some alternatives you can use in place of unsupported data types.

Data Type	Workaround
geometry	varbinary
geography	varbinary
hierarchyid	nvarchar(4000)
image	varbinary
text	varchar
ntext	nvarchar
sql_variant	Split column into several strongly typed columns.
table	Convert to temporary tables.
timestamp	Rework code to use datetime2 and CURRENT_TIMESTAMP function. Only constants are supported as defaults, therefore current_timestamp cannot be defined as a default constraint. If you need to migrate row version values from a timestamp typed column then use BINARY(8) or VARBINARY(8) for NOT NULL or NULL row version values.
xml	varchar
user defined types	convert back to their native types where possible
default values	default values support literals and constants only. Non-deterministic expressions or functions, such as GETDATE() or CURRENT_TIMESTAMP, are not supported.

The below SQL can be run on your current SQL database to identify columns which are not be supported by Azure SQL Data Warehouse:

SELECT  t.[name], c.[name], c.[system_type_id], c.[user_type_id], y.[is_user_defined], y.[name]
FROM sys.tables  t
JOIN sys.columns c on t.[object_id]    = c.[object_id]
JOIN sys.types   y on c.[user_type_id] = y.[user_type_id]
WHERE y.[name] IN ('geography','geometry','hierarchyid','image','text','ntext','sql_variant','timestamp','xml')
 AND  y.[is_user_defined] = 1;