Skip to content

Latest commit

 

History

History
128 lines (108 loc) · 6.58 KB

sql-data-warehouse-tables-data-types.md

File metadata and controls

128 lines (108 loc) · 6.58 KB
title description services documentationcenter author manager editor ms.assetid ms.service ms.devlang ms.topic ms.tgt_pltfrm ms.workload ms.custom ms.date ms.author
Data types guidance - Azure SQL Data Warehouse | Microsoft Docs
Recommendations to define data types that are compatible with SQL Data Warehouse.
sql-data-warehouse
NA
barbkess
jenniehubbard
d4a1f0a3-ba9f-44b9-95f6-16a4f30746d6
sql-data-warehouse
NA
article
NA
data-services
tables
12/06/2017
barbkess

Guidance for defining data types for tables in SQL Data Warehouse

Use these recommendations to define table data types that are compatible with SQL Data Warehouse. In addition to compatibility, minimizing the size of data types improves query performance.

SQL Data Warehouse 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 are using Polybase to load your tables, the defined length of the table row cannot 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 are migrating your database from another SQL database, you might encounter data types that are not supported in SQL Data Warehouse. Use this 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','timestamp','xml')
 AND  y.[is_user_defined] = 1;

Use workarounds for unsupported data types

The following list shows the data types that SQL Data Warehouse does not support and gives alternatives that you can use instead of the 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 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 type Convert back to the native data type when possible.
default values Default values support literals and constants only. Non-deterministic expressions or functions, such as GETDATE() or CURRENT_TIMESTAMP, are not supported.

Next steps

To learn more, see: