大网数据广告活动
数据库与应用安全

CREATE TABLE(Azure SQL 数据仓库)

· 52CE 我爱测

注意:本文中有关 SQL 数据仓库的讨论同时适用于 SQL 数据仓库以及并行数据仓库,除非另有说明。

适用对象:noSQL ServernoAzure SQL 数据库yesAzure SQL 数据仓库yes并行数据仓库

在 SQL 数据仓库 或 并行数据仓库 中创建新表。

若要了解表以及如何使用它们,请参阅 SQL 数据仓库中的表。

注意:本文中有关 SQL 数据仓库的讨论同时适用于 SQL 数据仓库以及并行数据仓库,除非另有说明。

主题链接图标 TRANSACT-SQL 语法约定

语法

-- Create a new table. 
CREATE TABLE [ database_name . [ schema_name ] . | schema_name. ] table_name   
    ( 
      { column_name <data_type>  [ <column_options> ] } [ ,...n ]   
    )  
    [ WITH [ <table_option> [ ,...n ] ) ]  
[;]  
   
<column_options> ::=
    [ COLLATE Windows_collation_name ]  
    [ NULL | NOT NULL ] -- default is NULL  
    [ [ CONSTRAINT constraint_name ] DEFAULT constant_expression  ]
  
<table_option> ::= 
    {   
        CLUSTERED COLUMNSTORE INDEX --default for SQL Data Warehouse 
      | HEAP --default for Parallel Data Warehouse   
      | CLUSTERED INDEX ( { index_column_name [ ASC | DESC ] } [ ,...n ] ) -- default is ASC 
    }  
    { 
        DISTRIBUTION = HASH ( distribution_column_name ) 
      | DISTRIBUTION = ROUND_ROBIN -- default for SQL Data Warehouse
      | DISTRIBUTION = REPLICATE -- default for Parallel Data Warehouse
    }   
    | PARTITION ( partition_column_name RANGE [ LEFT | RIGHT ] -- default is LEFT  
        FOR VALUES ( [ boundary_value [,...n] ] ) )  
  
<data type> ::=   
      datetimeoffset [ ( n ) ]  
    | datetime2 [ ( n ) ]  
    | datetime  
    | smalldatetime  
    | date  
    | time [ ( n ) ]  
    | float [ ( n ) ]  
    | real [ ( n ) ]  
    | decimal [ ( precision [ , scale ] ) ]   
    | numeric [ ( precision [ , scale ] ) ]   
    | money  
    | smallmoney  
    | bigint  
    | int   
    | smallint  
    | tinyint  
    | bit  
    | nvarchar [ ( n | max ) ]  -- max applies only to SQL Data Warehouse 
    | nchar [ ( n ) ]  
    | varchar [ ( n | max )  ] -- max applies only to SQL Data Warehouse  
    | char [ ( n ) ]  
    | varbinary [ ( n | max ) ] -- max applies only to SQL Data Warehouse  
    | binary [ ( n ) ]  
    | uniqueidentifier  

参数

database_name
将包含新表的数据库的名称。 默认为当前数据库。

schema_name
表的架构。 可选择指定架构。 如果是空白,将使用默认架构。

table_name
新表的名称。 若要创建本地临时表,请在表名前加上 #。 有关临时表的说明和指南,请参阅 Azure SQL 数据仓库中的临时表。

column_name
表列的名称。

列选项

COLLATE Windows_collation_name
指定表达式的排序规则。 此排序规则必须是 SQL Server 支持的 Windows 排序规则之一。 有关 SQL Server 支持的 Windows 排序规则列表,请参阅 Windows 排序规则名称 (Transact-SQL)/)。

NULL | NOT NULL
指定列中是否允许使用 NULL 值。 默认值为 NULL。

[ CONSTRAINT constraint_name ] DEFAULT constant_expression
指定默认列值。

参数 解释
constraint_name 约束的可选名称。 该约束名称在数据库中是唯一的。 此名称可在其他数据库中重复使用。
constant_expression 列的默认值。 表达式必须是文本值或一个常数。 例如,允许的常数表达式:'CA'、4。 不允许:2+3、CURRENT_TIMESTAMP。

表结构选项

有关选择表类型的指南,请参阅为 Azure SQL 数据仓库中的表编制索引。

CLUSTERED COLUMNSTORE INDEX
将表存储为聚集列存储索引。 聚集列存储索引应用于所有表数据。 这是 SQL 数据仓库 的默认值。

HEAP
将表存储为堆。 这是 并行数据仓库 的默认值。

CLUSTERED INDEX ( index_column_name [ ,...n ] )
将表存储为具有一个或多个键列的聚集索引。 这会按行存储数据。 在索引中使用 index_column_name 来指定一个或多个键列的名称。 有关详细信息,请参阅常规注释中的行存储表。

LOCATION = USER_DB
已弃用此选项。 虽然在语法上接受此选项,但此选项不再需要且不再影响行为。

表分发选项

若要了解如何选择最佳分发方法并使用分布式表,请参阅在 Azure SQL 数据仓库中分发表。

DISTRIBUTION = HASH ( distribution_column_name )
通过哈希存储在 distribution_column_name 中的值,将每行分配到一个分发。 该算法是确定性的,这意味着它总是将相同的值哈希到相同的分发。 分发列应该定义为 NOT NULL,因为所有具有 NULL 的行将分配到相同的分发。

DISTRIBUTION = ROUND_ROBIN
以轮循机制在所有分发上均匀地分发行。 这是 SQL 数据仓库 的默认值。

DISTRIBUTION = REPLICATE
将表的一个副本存储在每个 Compute 节点上。 对于 SQL 数据仓库,表存储在每个 Compute 节点上的分发数据库上。 对于 并行数据仓库,表存储在跨 Compute 节点的 SQL Server 文件组中。 这是 并行数据仓库 的默认值。

表分区选项

有关使用表分区的指南,请参阅对 SQL 数据仓库中的表进行分区。

PARTITION ( partition_column_name RANGE [ LEFT | RIGHT ] FOR VALUES ( [ boundary_value [,...n] ] ))
创建一个或多个表分区。 这些是水平表切片,可对行的子集执行操作,而不管该表是作为堆、聚集索引还是聚集列存储索引存储。 与分发列不同,表分区不能决定存储每行的分发。 表分区决定行如何分组并存储在每个分发中。

参数 解释
partition_column_name 指定 SQL 数据仓库 将用于行分区的列。 此列可以是任何数据类型。 SQL 数据仓库 按升序对分区列值进行排序。 出于 RANGE 规范的目的,由 LEFT 到 RIGHT 由低到高排序。
RANGE LEFT 指定属于左侧分区的边界值(较低值)。 默认为“左”。
RANGE RIGHT 指定属于右侧分区的边界值(较高值)。
FOR VALUES ( boundary_value [,...n] ) 指定分区的边界值。 boundary_value 是一个常数表达式。 不可为 NULL。 它必须匹配或可以隐式转换为 partition_column_name 的数据类型。 它不能在隐式转换期间截断,使得值的大小和规模与 partition_column_name 的数据类型不匹配如果指定 PARTITION 子句,但不指定边界值,SQL 数据仓库 将创建具有一个分区的已分区表。 如果适用,稍后可将表格拆分成两个分区。如果指定一个边界值,生成的表格有两个分区;一个用于低于边界值的值,另一个用于高于边界值的值。 请注意,如果将分区移动到未分区表中,未分区表将接收数据,但其元数据中将不会有分区边界。

请在“示例”部分中查看创建已分区表。

数据类型

SQL 数据仓库 支持最常用的数据类型。 以下是支持的数据类型及其详细信息和存储字节的列表。 若要更好地理解数据类型以及如何使用它们,请参阅 SQL 数据仓库中表的数据类型。

有关数据类型转换的表,请参阅 CAST 和 CONVERT (Transact-SQL) 的“隐式转换”部分。

datetimeoffset [ ( n ) ]
n 的默认值为 7。

datetime2 [ ( n ) ]
与 datetime 相同,只不过可以指定秒小数的数值。 n 的默认值为 7。

n 值 精度 小数位数
0 19 0
1 21 1
2 22 2
3 23 3
4 24 4
5 25 5
6 26 6
7 27 7

datetime
根据公历,用 19 到 23 个字符存储日期和时间。 日期可以包含年、月和日。 时间包含小时、分钟、秒。可选择显示小数秒的三位数。 存储大小为 8 个字节。

smalldatetime
存储日期和时间。 存储大小为 4 个字节。

date
根据公历,使用最多 10 个字符的年、月和日来存储日期。 存储大小为 3 个字节。 日期存储为整数。

time [ ( n ) ]
n 的默认值为 7。

float [ ( n ) ]
用于表示浮点数值数据的近似数值数据类型。 浮点数据为近似值;也就是说,并非数据类型范围内的所有值都能精确地表示。 n 指定用于存储科学记数法中 float 尾数的字节数。 因此,n 指示精度和存储大小。 如果指定了 n,它必须是介于 1 和 53 之间的某个值。 n 的默认值为 53。

n 值 精度 存储大小
1-24 7 位数 4 个字节
25-53 15 位数 8 字节

SQL 数据仓库 将 n 视为下列两个可能值之一。 如果 1<= n <= 24,将 n 视为 24。 如果 25 <= n <= 53,将 n 视为 53。

SQL 数据仓库 float 数据类型从 1 到 53 之间的所有 n 值均符合 ISO 标准。 double precision 的同义词是 float(53)。

real [ ( n ) ]
real 的定义与 float 相同。 real 的 ISO 同义词为 float(24)。

decimal [ ( precision [ , scale ] ) ] | numeric [ ( precision [ , scale ] ) ]
存储固定的精度和小数位数。

精度
最多可以存储的十进制数字的总位数,包括小数点左边和右边的位数。 该精度的取值范围必须为 1 到最大精度 38。 默认精度为 18。

scale
小数点右边可以存储的十进制数字的最大位数。 小数位数的取值范围必须为 0 到精度。 只有指定了精度,才能指定小数位数。 默认小数位数为 0;因此,0 <= 小数位数 <= 精度。 最大存储大小基于精度而变化。

精度 存储字节数
1-9 5
10-19 9
20-28 13
29-38 17

money | smallmoney
表示货币值的数据类型。

数据类型 存储字节数
money 8
smallmoney 4

bigint | int | smallint | tinyint
使用整数数据的精确数字数据类型。 存储如下表所示。

数据类型 存储字节数
bigint 8
int 4
smallint 2
tinyint 1

bit
可以取值为 1、0 或 `NULL 的 integer 数据类型。 SQL 数据仓库 可优化 bit 列的存储。 如果表中的 bit 列为 8 列或更少,这些列作为 1 个字节存储。 如果 bit 列为 9 到 16 列,这些列作为 2 个字节存储,以此类推。

nvarchar [ ( n | max ) ] -- max 仅适用于 SQL 数据仓库。
可变长度 Unicode 字符数据。 n 的取值范围为 1 至 4,000。 max 指示最大存储大小是 2^31-1 个字节 (2 GB)。 存储大小(以字节为单位)是所输入字符个数的两倍 + 2 个字节。 所输入数据的长度可以为 0 个字符。

nchar [ ( n ) ]
固定长度 Unicode 字符数据,长度为 n 个字节。 n 的取值范围必须为 1 到 4000。 存储大小为 n 字节的两倍。

varchar [ ( n | max ) ] -- max 仅适用于 SQL 数据仓库。
可变长度非 Unicode 字符数据,长度为 n 个字节。 n 的取值范围必须为 1 到 8000。 max 指示最大存储大小为 2 ^31-1 个字节 (2 GB)。存储大小是输入数据的实际长度 + 2 个字节。

char [ ( n ) ]
固定长度非 Unicode 字符数据,长度为 n 个字节。 n 的取值范围必须为 1 到 8000。 存储大小为 n 字节。 n 的默认值为 1。

varbinary [ ( n | max ) ] -- max 仅适用于 SQL 数据仓库。
可变长度二进制数据。 n 的取值范围为 1 到 8000。 max 指示最大存储大小是 2^31-1 个字节 (2 GB)。 存储大小是输入数据的实际长度加 2 个字节。 n 的默认值为 7。

binary [ ( n ) ]
固定长度二进制数据,长度为 n 个字节。 n 的取值范围为 1 到 8000。 存储大小为 n 字节。 n 的默认值为 7。

uniqueidentifier
16 字节 GUID。

Permissions

创建表需要 db_ddladmin 固定数据库角色的权限,或者:

  • 数据库的 CREATE TABLE 权限
  • 将包含表的架构的 ALTER SCHEMA 权限。

创建已分区表需要 db_ddladmin 固定数据库角色的权限,或者

  • ALTER ANY DATASPACE 权限

创建本地临时表的登录名在该表上获取 CONTROL``INSERT``SELECT 和 UPDATE 权限。

一般备注

有关最小和最大限制,请参阅 SQL 数据仓库容量限制。

确定表分区的数目

每个用户定义的表格划分为多个较小的表格,这些表格存储在名为分发的不同位置。 SQL 数据仓库 使用 60 个分发。 在 并行数据仓库 中,分发的数目取决于 Compute 节点的数目。

每个分发包含所有的表分区。 例如,如果有 60 个分发和 4 个表分区,则会有 320 个分区。 如果该表是一个群集列存储索引,每个分区将有一个列存储索引,这意味着你将拥有 320 个列存储索引。

我们建议使用更少的表分区,确保每个列存储索引具有足够的行以充分利用列存储索引的优势。 有关更多指南,请参阅对 SQL 数据仓库中的表进行分区和为 SQL 数据仓库中的表编制索引

行存储表(堆或聚集索引)

行存储表是以逐行顺序存储的表。 它是堆或聚集索引。 SQL 数据仓库 创建所有包含页压缩的行存储表;这不是用户可配置的。

列存储表(列存储索引)

列存储表是以逐列顺序存储的表。 列存储索引是管理存储在列存储表中的数据的技术。 聚集列存储索引不影响数据的分发方式;它会影响数据在每个分发中的存储方式。

若要将行存储表更改为列存储表,请删除表上所有现有索引并创建一个聚集列存储索引。 有关示例,请参阅 CREATE COLUMNSTORE INDEX (Transact-SQL)。

有关详细信息,请参阅以下文章:

限制和局限

无法在分发列上定义 DEFAULT 约束。

“度量值组”

使用分区时,分区列不能使用仅 Unicode 排序规则。 例如,以下语句将失败。

CREATE TABLE t1 ( c1 varchar(20) COLLATE Divehi_90_CI_AS_KS_WS) WITH (PARTITION (c1 RANGE FOR VALUES (N'')))

如果 boundary_value 是必须隐式转换为 partition_column_name 中数据类型的文本值,会出现差异。 通过 SQL 数据仓库 系统视图显示文本值,但转换后的值用于 Transact-SQL 操作。

临时表

不支持以 ## 开头的全局临时表。

本地临时表具有以下限制和约束:

  • 仅对当前会话可见。 在会话末尾 SQL 数据仓库 会自动删除它们。 若要显式删除它们,请使用 DROP TABLE 语句。
  • 不能对其重命名。
  • 它们不能有分区或视图。
  • 不能更改它们的权限。 GRANT、DENY 和 REVOKE 语句不能用于本地临时表。
  • 为临时表阻止数据库控制台命令。
  • 如果批处理中使用多个本地临时表,每个临时表都必须具有唯一的名称。 如果多个会话正在运行同一批处理并创建相同的本地临时表,SQL 数据仓库 会以内部方式为本地临时表名追加一个数字后缀,为每个本地临时表保留唯一的名称。

锁定行为

对表采用排他锁。 在 DATABASE、SCHEMA、SCHEMARESOLUTION 对象上采用共享锁。

列的示例

A. 指定一个列排序规则

在以下示例中,使用两种不同的列排序规则创建表 MyTable。 默认情况下,列 mycolumn1 具有默认的排序规则 Latin1_General_100_CI_AS_KS_WS。 列 mycolumn2 具有排序规则 Frisian_100_CS_AS。

CREATE TABLE MyTable   
  (  
    mycolumnnn1 nvarchar,  
    mycolumn2 nvarchar COLLATE Frisian_100_CS_AS )  
WITH ( CLUSTERED COLUMNSTORE INDEX )  
;  
  

B. 指定列的 DEFAULT 约束

以下示例显示了为列指定默认值的语法。 colA 列有一个名为 constraint_colA 的默认约束以及一个默认值 0。

  
CREATE TABLE MyTable   
  (  
    colA int CONSTRAINT constraint_colA DEFAULT 0,  
    colB nvarchar COLLATE Frisian_100_CS_AS   
  )  
WITH ( CLUSTERED COLUMNSTORE INDEX )  
;  

临时表的示例

C. 创建本地临时表

以下示例创建一个名为 #myTable 的本地临时表。 该表由 3 部分名称指定。 临时表名称以 # 开头。

CREATE TABLE AdventureWorks.dbo.#myTable   
  (  
   id int NOT NULL,  
   lastName varchar(20),  
   zipCode varchar(6)  
  )  
WITH  
  (   
    DISTRIBUTION = HASH (id),  
    CLUSTERED COLUMNSTORE INDEX   
  )  
;  

表结构的示例

D. 创建一个具有聚集列存储索引的表

以下示例创建一个具有聚集列存储索引的分布式表。 将每个分发存储为一个列存储。

聚集列存储索引不影响数据的分发方式;数据总是按行分发。 聚集列存储索引影响数据在每个分发中的存储方式。

  
CREATE TABLE MyTable   
  (  
    colA int CONSTRAINT constraint_colA DEFAULT 0,  
    colB nvarchar COLLATE Frisian_100_CS_AS   
  )  
WITH   
  (   
    DISTRIBUTION = HASH ( colB ),  
    CLUSTERED COLUMNSTORE INDEX   
  )  
;  

表分发的示例

E. 创建 ROUND_ROBIN 表

以下示例创建 ROUND_ROBIN 表,其中包含三列并且没有分区。 数据分布在所有分发中。 该表是使用 CLUSTERED COLUMNSTORE INDEX 创建的,它能提供比堆或行存储聚集索引更好的性能和数据压缩。

CREATE TABLE myTable   
  (  
    id int NOT NULL,  
    lastName varchar(20),  
    zipCode varchar(6)  
  )  
WITH ( CLUSTERED COLUMNSTORE INDEX );  

F. 创建哈希分布式表

以下示例创建与上面的示例相同的表。 但对于此表,分发行(位于 id 列),而不是像 ROUND_ROBIN 表一样随机分发。 该表是使用 CLUSTERED COLUMNSTORE INDEX 创建的,它能提供比堆或行存储聚集索引更好的性能和数据压缩。

CREATE TABLE myTable   
  (  
    id int NOT NULL,  
    lastName varchar(20),  
    zipCode varchar(6)  
  )  
WITH  
  (   
    DISTRIBUTION = HASH (id),   
    CLUSTERED COLUMNSTORE INDEX  
  );  

G. 创建已复制的表

以下示例创建一个类似于前面示例的已复制表。 将已复制表全部复制到每个 Compute 节点。 通过每个 Compute 节点上的副本,可以减少查询的数据移动。 此示例是用 CLUSTERED INDEX 创建的,它能提供比堆更好的数据压缩,并且可能没有足够的行来实现良好的 CLUSTERED COLUMNSTORE INDEX 压缩。

CREATE TABLE myTable   
  (  
    id int NOT NULL,  
    lastName varchar(20),  
    zipCode varchar(6)  
  )  
WITH  
  (   
    DISTRIBUTION = REPLICATE,   
    CLUSTERED INDEX (lastName)  
  );  

表分区的示例

H. 创建已分区表

以下示例创建与示例 A 中所示相同的表,并在 id 列上添加 RANGE LEFT 分区。 它指定了四个分区边界值,所以有五个分区。

CREATE TABLE myTable   
  (  
    id int NOT NULL,  
    lastName varchar(20),  
    zipCode int)  
WITH   
  (   
  
    PARTITION ( id RANGE LEFT FOR VALUES (10, 20, 30, 40 )),  
    CLUSTERED COLUMNSTORE INDEX      
  )  
;  

在此示例中,数据将分类到以下分区中:

  • 分区 1:列 <= 10
  • 分区 2:10 < 列 <= 20
  • 分区 3:20 < 列 <= 30
  • 分区 4:30 < 列 <= 40
  • 分区 5:40 < 列

如果将此同一个表分区为 RANGE RIGHT 而非 RANGE LEFT(默认),数据将分类到以下分区中:

  • 分区 1:列 < 10
  • 分区 2:10 <= 列 < 20
  • 分区 3:20 <= 列 < 30
  • 分区 4:30 <= 列 < 40
  • 分区 5:40 <= 列

I. 使用一个分区创建已分区表

以下示例使用一个分区创建已分区表。 它不指定任何边界值,所以有一个分区。

CREATE TABLE myTable (  
    id int NOT NULL,  
    lastName varchar(20),  
    zipCode int)  
WITH   
    (   
      PARTITION ( id RANGE LEFT FOR VALUES ( )),  
      CLUSTERED COLUMNSTORE INDEX  
    )  
;  

J. 创建具有日期分区的表

以下示例创建一个名为 myTable 的新表,并在 date 列上进行分区。 使用 RANGE RIGHT 和日期作为边界值,它将在每个分区中放置一个月的数据。

CREATE TABLE myTable (  
    l_orderkey      bigint,       
    l_partkey       bigint,                                             
    l_suppkey       bigint,                                           
    l_linenumber    bigint,        
    l_quantity      decimal(15,2),  
    l_extendedprice decimal(15,2),  
    l_discount      decimal(15,2),  
    l_tax           decimal(15,2),  
    l_returnflag    char(1),  
    l_linestatus    char(1),  
    l_shipdate      date,  
    l_commitdate    date,  
    l_receiptdate   date,  
    l_shipinstruct  char(25),  
    l_shipmode      char(10),  
    l_comment       varchar(44))  
WITH   
  (   
    DISTRIBUTION = HASH (l_orderkey),  
    CLUSTERED COLUMNSTORE INDEX,  
    PARTITION ( l_shipdate  RANGE RIGHT FOR VALUES   
      (  
        '1992-01-01','1992-02-01','1992-03-01','1992-04-01','1992-05-01',
        '1992-06-01','1992-07-01','1992-08-01','1992-09-01','1992-10-01',
        '1992-11-01','1992-12-01','1993-01-01','1993-02-01','1993-03-01',
        '1993-04-01','1993-05-01','1993-06-01','1993-07-01','1993-08-01',
        '1993-09-01','1993-10-01','1993-11-01','1993-12-01','1994-01-01',
        '1994-02-01','1994-03-01','1994-04-01','1994-05-01','1994-06-01',
        '1994-07-01','1994-08-01','1994-09-01','1994-10-01','1994-11-01',
        '1994-12-01'  
      ))
  );  

另请参阅

CREATE TABLE AS SELECT(Azure SQL 数据仓库)
DROP TABLE (Transact-SQL)
ALTER TABLE (Transact-SQL)

历史资料。此文对应 2018-11-30 的中文文档快照,适用产品版本和命令行为以原文为准。

来源与署名:Microsoft 和文档贡献者(原始作者元数据 CarlRabeler)。固定版本原文。文档采用 CC BY 4.0,示例代码采用 MIT 授权。本站展开固定版本仓库内的引用内容,将 Markdown 转为 HTML,规范化链接,移除外部媒体、发布器占位标签和导航标记。

资料来源与适用范围

资料来源
Microsoft / SQL Server 中文文档历史快照
适用范围
历史中文快照 2018-11-30;详见原文产品版本;文档日期 07/14/2017
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-16
授权说明
CC BY 4.0(文档)/ MIT(示例代码)

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。