Table data types for dedicated SQL pool (formerly SQL DW) in Azure Synapse Analytics

Artikkeli
02/18/2022

Included in this article are recommendations for defining table data types in dedicated SQL pool.

Supported data types

Dedicated SQL pool (formerly SQL DW) supports the most commonly used data types. For a list of the supported data types, see data types in the CREATE TABLE statement.

Minimize row length

Minimizing the size of data types shortens the row length, which leads to better query performance. Use the smallest data type that works for your data.

Avoid defining character columns with a large default length. For example, if the longest value is 25 characters, then define your column as VARCHAR(25).
Avoid using NVARCHAR when you only need VARCHAR.
When possible, use NVARCHAR(4000) or VARCHAR(8000) instead of NVARCHAR(MAX) or VARCHAR(MAX).

If you're using PolyBase external tables to load your tables, the defined length of the table row can't exceed 1 MB. When a row with variable-length data exceeds 1 MB, you can load the row with BCP, but not with PolyBase.

Identify unsupported data types

If you're migrating your database from another SQL database, you might find data types that aren't supported in dedicated SQL pool. Use the following query to discover unsupported data types in your existing SQL schema:

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','xml')
 AND  y.[is_user_defined] = 1;

Workarounds for unsupported data types

The following list shows the data types that dedicated SQL pool (formerly SQL DW) doesn't support and gives useful alternatives for unsupported data types.

Unsupported 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 the CURRENT_TIMESTAMP function. Only constants are supported as defaults, so current_timestamp can't be defined as a default constraint. If you need to migrate row version values from a timestamp typed column, use BINARY(8) or VARBINARY(8) for NOT NULL or NULL row version values.
xml	varchar
user-defined type	Convert back to the native data type when possible.
default values	Default values support literals and constants only.

Next steps

For more information on developing tables, see Table Overview.

Jaa