表迁移
表名
DWS不支持“数据库名.模式名.表名”的形式，需要对应转换为“模式名.表名”的形式。
|
SQL-Server语法 |
迁移后语法 |
|---|---|
|
CREATE TABLE `analytics-di-dev.abase.buyer_location` ( id_buyer INT, id_location INT ); |
CREATE TABLE "abase"."buyer_location" ("id_buyer" INT, "id_location" INT) WITH (ORIENTATION = ROW, COMPRESSION = NO) NOCOMPRESS DISTRIBUTE BY HASH ("id_buyer"); |
表级参数相关迁移
SQL-Server支持创建行压缩表，DWS不支持，迁移过程中删除。
|
SQL-Server语法 |
迁移后语法 |
|---|---|
|
CREATE TABLE dbo.T1 ( c1 INT, c2 NVARCHAR(200) ) WITH (DATA_COMPRESSION = ROW); |
CREATE TABLE "dbo"."t1" ("c1" INT, "c2" VARCHAR(200)) WITH (ORIENTATION = ROW, COMPRESSION = NO) NOCOMPRESS DISTRIBUTE BY HASH ("c1"); |
SQL-Server支持创建XML压缩表，DWS不支持，迁移过程中删除。
|
SQL-Server语法 |
迁移后语法 |
|---|---|
|
CREATE TABLE dbo.T1 ( c1 INT, c2 XML ) WITH (XML_COMPRESSION = ON); |
CREATE TABLE "dbo"."t1" ("c1" INT, "c2" TEXT) WITH (ORIENTATION = ROW, COMPRESSION = NO) NOCOMPRESS DISTRIBUTE BY HASH ("c1"); |
SQL-Server 支持TEXTIMAGE_ON参数，指示某些类型存储在指定文件组，DWS不支持，迁移过程中删除。
|
SQL-Server语法 |
迁移后语法 |
|---|---|
|
CREATE TABLE dbo.T1 ( c1 INT, c2 text ) TEXTIMAGE_ON "default"; |
CREATE TABLE "dbo"."t1" ("c1" INT, "c2" TEXT) WITH (ORIENTATION = ROW, COMPRESSION = NO) NOCOMPRESS DISTRIBUTE BY HASH ("c1"); |
SQL-Server 支持SYSTEM_VERSIONING参数，创建系统版本控制表，DWS不支持，迁移过程中删除。
|
SQL-Server语法 |
迁移后语法 |
|---|---|
|
CREATE TABLE Department ( DepartmentNumber CHAR(10) NOT NULL PRIMARY KEY, DepartmentName VARCHAR(50) NOT NULL, ManagerID INT NULL ) WITH (SYSTEM_VERSIONING = ON); |
CREATE TABLE "department" ( "departmentnumber" CHAR(10) NOT NULL PRIMARY KEY, "departmentname" VARCHAR(50) NOT NULL, "managerid" INT ) WITH (ORIENTATION = ROW, COMPRESSION = NO) NOCOMPRESS DISTRIBUTE BY HASH ("departmentnumber"); |
列级参数相关迁移
SQL-Server支持创建具有稀疏列的表，DWS不支持，迁移过程中删除。
|
SQL-Server语法 |
迁移后语法 |
|---|---|
|
CREATE TABLE dbo.T1 ( c1 INT PRIMARY KEY, c2 VARCHAR(50) SPARSE NULL ); |
CREATE TABLE "dbo"."t1" ("c1" INT PRIMARY KEY, "c2" VARCHAR(50)) WITH (ORIENTATION = ROW, COMPRESSION = NO) NOCOMPRESS DISTRIBUTE BY HASH ("c1"); |
SQL-Server支持FILESTREAM关键词，指定表的FILESTREAM数据位置，DWS不支持，迁移过程中删除。
|
SQL-Server语法 |
迁移后语法 |
|---|---|
|
CREATE TABLE dbo.EmployeePhoto ( EmployeeId INT NOT NULL PRIMARY KEY, Photo VARBINARY(MAX) FILESTREAM NULL, MyRowGuidColumn UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL UNIQUE DEFAULT NEWID() ); |
CREATE TABLE "dbo"."employeephoto" ( "employeeid" INT NOT NULL PRIMARY KEY, "photo" BYTEA, "myrowguidcolumn" TEXT NOT NULL ) WITH (ORIENTATION = ROW, COMPRESSION = NO) NOCOMPRESS DISTRIBUTE BY HASH ("employeeid"); CREATE INDEX "idx_employeephoto_myrowguidcolumn" ON "dbo"."employeephoto"("myrowguidcolumn"); |
SQL-Server 支持创建聚集索引或非聚集索引，DWS不支持，迁移过程中删除。
主键聚集索引
|
SQL-Server语法 |
迁移后语法 |
|---|---|
|
CREATE TABLE Department ( DepartmentNumber CHAR(10) NOT NULL PRIMARY KEY CLUSTERED, DepartmentName VARCHAR(50) NOT NULL, ManagerID INT NULL ); |
CREATE TABLE "department" ( "departmentnumber" CHAR(10) NOT NULL PRIMARY KEY, "departmentname" VARCHAR(50) NOT NULL, "managerid" INT ) WITH (ORIENTATION = ROW, COMPRESSION = NO) NOCOMPRESS DISTRIBUTE BY HASH ("departmentnumber"); |
唯一索引非聚集索引。
|
SQL-Server语法 |
迁移后语法 |
|---|---|
|
CREATE TABLE Department ( DepartmentNumber CHAR(10) NOT NULL UNIQUE NONCLUSTERED, DepartmentName VARCHAR(50) NOT NULL, ManagerID INT NULL ); |
CREATE TABLE "department" ( "departmentnumber" CHAR(10) NOT NULL UNIQUE, "departmentname" VARCHAR(50) NOT NULL, "managerid" INT ) WITH (ORIENTATION = ROW, COMPRESSION = NO) NOCOMPRESS DISTRIBUTE BY HASH ("departmentnumber"); |
