Azure Synapse Analytics 中专用 SQL 池(以前称为 SQL DW)的表数据类型Table data types for dedicated SQL pool (formerly SQL DW) in Azure Synapse Analytics

本文涵盖了在专用 SQL 池中定义表数据类型的建议。Included in this article are recommendations for defining table data types in dedicated SQL pool.

支持的数据类型Supported data types

专用 SQL 池(以前称为 SQL DW)支持最常用的数据类型。Dedicated SQL pool (formerly SQL DW) supports the most commonly used data types. 有关受支持数据类型的列表,请参阅 CREATE TABLE 语句中的数据类型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. 例如,如果最长的值是 25 个字符,则将列定义为 VARCHAR(25)。For example, if the longest value is 25 characters, then define your column as VARCHAR(25).
  • 仅需要 VARCHAR 时请避免使用 NVARCHARAvoid using NVARCHAR when you only need VARCHAR.
  • 尽可能使用 NVARCHAR(4000) 或 VARCHAR(8000),而非 NVARCHAR(MAX) 或 VARCHAR(MAX)。When possible, use NVARCHAR(4000) or VARCHAR(8000) instead of NVARCHAR(MAX) or VARCHAR(MAX).

如果使用 PolyBase 外部表来加载表,则定义的表行长度不能超过 1 MB。If you're using PolyBase external tables to load your tables, the defined length of the table row can't exceed 1 MB. 当数据长度可变的行超过 1 MB 时,可使用 BCP 而不是 PolyBase 加载行。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

如果从另一个 SQL 数据库迁移你的数据库,你可能会发现专用 SQL 池不支持的数据类型。If you're migrating your database from another SQL database, you might find data types that aren't supported in dedicated SQL pool. 可以使用以下查询查明现有 SQL 架构不支持的数据类型: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

以下列表显示了专用 SQL 池(以前称为 SQL DW)不支持的数据类型,同时提供了不受支持的数据类型的有效替代数据类型。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
geometrygeometry varbinaryvarbinary
geographygeography varbinaryvarbinary
hierarchyidhierarchyid nvarchar(4000)nvarchar(4000)
imageimage varbinaryvarbinary
texttext varcharvarchar
ntextntext nvarcharnvarchar
sql_variantsql_variant 将列拆分成多个强类型化列。Split column into several strongly typed columns.
tabletable 转换成暂时表。Convert to temporary tables.
timestamptimestamp 重写代码来使用 datetime2CURRENT_TIMESTAMP 函数。Rework code to use datetime2 and the CURRENT_TIMESTAMP function. 仅支持使用常量作为默认值,因此,不能将 current_timestamp 定义为默认约束。Only constants are supported as defaults, so current_timestamp can't be defined as a default constraint. 如果需要从 timestamp 类型的列迁移行版本值,请为 NOT NULL 或 NULL 行版本值使用 BINARY(8) 或 VARBINARY(8)。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.
xmlxml varcharvarchar
用户定义的类型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.