本文深入探讨SQL建表语句，包括列选项、表结构、表分发和表分区的详细说明。介绍了如何指定列的排序规则、默认值、创建聚集列存储索引、哈希分布式表和已分区表等，并提供了丰富的示例。
目录
（一）前言
本文我们来探讨一个看似比较简单但实际上说清楚需要很多门道的操作，即建表语句以及语句中相关参数的含义，接下来让我们来具体看下一些建表基本事项。
（二）建表语法
-- Create a new table.
CREATE TABLE { database_name.schema_name.table_name | schema_name.table_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
[ IDENTITY [ ( seed, increment ) ]
[ <column_constraint> ]
<column_constraint>::=
{
DEFAULT constant_expression
| PRIMARY KEY NONCLUSTERED NOT ENFORCED -- Applies to Azure Synapse Analytics only
| UNIQUE NOT ENFORCED -- Applies to Azure Synapse Analytics only
}
<table_option> ::=
{
CLUSTERED COLUMNSTORE INDEX -- default for Azure Synapse Analytics
| CLUSTERED COLUMNSTORE INDEX ORDER (column [,...n])
| 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 Azure Synapse Analytics
| 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 Azure Synapse Analytics
| nchar [ ( n ) ]
| varchar [ ( n | max ) ] -- max applies only to Azure Synapse Analytics
| char [ ( n ) ]
| varbinary [ ( n | max ) ] -- max applies only to Azure Synapse Analytics
| binary [ ( n ) ]
| uniqueidentifier（三）参数说明
database_name
将包含新表的数据库的名称。 默认为当前数据库。
schema_name
表的架构。 可选择指定架构 。 如果是空白，将使用默认架构。
table_name
新表的名称。 若要创建本地临时表，请在表名前加上 #。
column_name
表列的名称。
1. 列选项
COLLATE Windows_collation_name
指定表达式的排序规则。 此排序规则必须是 SQL Server 支持的 Windows 排序规则之一。
NULL | NOT NULL
指定列中是否允许使用 NULL 值。 默认为 NULL。
[ CONSTRAINTconstraint_name ] DEFAULTconstant_expression
指定默认列值。
| 参数 | 说明 |
|---|---|
| constraint_name | 约束的可选名称。 该约束名称在数据库中是唯一的。 此名称可以重用于其他数据库。 |
| constant_expression | 列的默认值。 表达式必须是文本值或一个常数。 例如，允许的常数表达式：'CA'、4。 禁止使用这些常量表达式：2+3、CURRENT_TIMESTAMP。 |
2. 表结构选项
CLUSTERED COLUMNSTORE INDEX
将表存储为聚集列存储索引。 聚集列存储索引应用于所有表数据。 这是 Azure Synapse Analytics 的默认行为。
HEAP：将表存储为堆。 这是 Analytics Platform System (PDW) 的默认行为。
CLUSTERED INDEX ( index_column_name [ ,...n ] )
将表存储为具有一个或多个键列的聚集索引。 此行为按行存储数据。 在索引中使用 index_column_name 来指定一个或多个键列的名称 。 有关详细信息，请参阅常规注释中的行存储表。
LOCATION = USER_DB：此选项已遭弃用。 虽然在语法上可接受，但已不再需要它，而且它也不再影响行为。
3. 表分发选项
DISTRIBUTION = HASH (distribution_column_name )：通过哈希处理 distribution_column_name 中存储的值，将每行都分配到一个分发。 算法是确定性的。也就是说，它总是将相同的值哈希到相同的分发。 应将分发列定义为 NOT NULL，因为所有包含 NULL 值的行都分配到相同的分发。
DISTRIBUTION = ROUND_ROBIN：以轮循机制在所有分发上均匀地分发行。 这是 Azure Synapse Analytics 的默认行为。
DISTRIBUTION = REPLICATE：将表的一个副本存储在每个 Compute 节点上。 对于 Azure Synapse Analytics，表存储在每个 Compute 节点上的分发数据库上。 对于 Analytics Platform System (PDW)，表存储在跨 Compute 节点的 SQL Server 文件组中。 这是 Analytics Platform System (PDW) 的默认行为。
4. 表分区选项
PARTITION ( partition_column_name RANGE [ LEFT | RIGHT ] FOR VALUES ( [ boundary_value [,...n] ] ))
创建一个或多个表分区。 这些分区是水平表切片，可便于向行的子集应用操作，无论表是作为堆、聚集索引还是聚集列存储索引进行存储。 与分发列不同，表分区不确定存储每行的分发。 表分区决定行如何分组并存储在每个分发中。
| 参数 | 说明 |
|---|---|
| partition_column_name | 指定 Azure Synapse Analytics 将用于行分区的列。 此列可以是任何数据类型。 Azure Synapse Analytics 按升序对分区列值进行排序。 在 LEFT 规范中，由低到高的排序是从 RIGHT 到 RANGE。 |
RANGE LEFT | 指定属于左侧分区的边界值（较低值）。 默认为“左”。 |
RANGE RIGHT | 指定属于右侧分区的边界值（较高值）。 |
FOR VALUES ( boundary_value [,...n] ) | 指定分区的边界值。 boundary_value 是一个常数表达式 。 它不得为 NULL。 它必须匹配或可以隐式转换为 partition_column_name 的数据类型 。 无法在隐式转换期间截断它，这样值的大小和确定位数与 partition_column_name 的数据类型不匹配 如果你指定 PARTITION 子句，但不指定边界值，Azure Synapse Analytics 会创建包含一个分区的已分区表。 如果适用，稍后可以将表拆分成两个分区。如果指定一个边界值，生成的表格有两个分区；一个用于低于边界值的值，另一个用于高于边界值的值。 如果你将分区移到未分区表中，未分区表会接收数据，但它的元数据中不会有分区边界。 |
（四）实例
1. 列的示例
(1) 指定一个列排序规则
在以下示例中，使用两种不同的列排序规则创建表 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 )
;（2） 指定列的 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 )
;2. 表结构的示例
（1）创建一个具有聚集列存储索引的表
以下示例创建一个具有聚集列存储索引的分布式表。 将每个分发存储为一个列存储。
聚集列存储索引不影响数据的分发方式；数据始终按行分发。 聚集列存储索引影响数据在每个分发中的存储方式。
CREATE TABLE MyTable
(
colA int CONSTRAINT constraint_colA DEFAULT 0,
colB nvarchar COLLATE Frisian_100_CS_AS
)
WITH
(
DISTRIBUTION = HASH ( colB ),
CLUSTERED COLUMNSTORE INDEX
)
;（2）创建有序聚集列存储索引
下面的示例展示了如何创建有序聚集列存储索引。 索引按 SHIPDATE 进行排序。
CREATE TABLE Lineitem
WITH (DISTRIBUTION = ROUND_ROBIN, CLUSTERED COLUMNSTORE INDEX ORDER(SHIPDATE))
AS
SELECT * FROM ext_Lineitem3. 表分发的示例
（1）创建 ROUND_ROBIN 表
以下示例创建 ROUND_ROBIN 表，其中包含三列并且没有分区。 数据分布在所有分发中。 该表是使用 CLUSTERED COLUMNSTORE INDEX 创建的，它能提供比堆或行存储聚集索引更好的性能和数据压缩。
CREATE TABLE myTable
(
id int NOT NULL,
lastName varchar(20),
zipCode varchar(6)
)
WITH ( CLUSTERED COLUMNSTORE INDEX );（2）创建哈希分布式表
以下示例创建与上面的示例相同的表。 但对于此表，分发行（位于 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
);（3）创建已复制的表
以下示例创建一个类似于前面示例的已复制表。 将已复制表全部复制到每个 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)
);4. 表分区的示例
（1）创建已分区表
以下示例创建与示例 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 <= 列
（2）使用一个分区创建已分区表
以下示例使用一个分区创建已分区表。 它不指定任何边界值，所以有一个分区。
CREATE TABLE myTable (
id int NOT NULL,
lastName varchar(20),
zipCode int)
WITH
(
PARTITION ( id RANGE LEFT FOR VALUES ( )),
CLUSTERED COLUMNSTORE INDEX
)
;（3）创建具有日期分区的表
以下示例创建一个名为 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'
))
);
扫一扫
519
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
