Migrating Tables¶
Table Name¶
GaussDB(DWS) does not support the Database name.Schema name.Table name format. You need to convert it to the Schema name.Table name format.
SQL Server Syntax | Syntax After Migration |
|---|---|
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"); |
Migration of Table-Level Parameters¶
SQL Server supports the creation of row-compressed tables, while GaussDB(DWS) does not. The tables are deleted during migration.
SQL Server Syntax | Syntax After Migration |
|---|---|
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 supports the creation of compressed XML tables, while GaussDB(DWS) does not. The tables are deleted during migration.
SQL Server Syntax | Syntax After Migration |
|---|---|
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 supports the TEXTIMAGE_ON parameter, which indicates that some types of data are stored in a specified file group. GaussDB(DWS) does not support this parameter and deletes it during migration.
SQL Server Syntax | Syntax After Migration |
|---|---|
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 supports the SYSTEM_VERSIONING parameter, which is used to create a system versioning table. GaussDB(DWS) does not support this parameter and deletes it during migration.
SQL Server Syntax | Syntax After Migration |
|---|---|
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"); |
Migration of Column-Level Parameters¶
SQL Server supports the creation of tables with sparse columns, but GaussDB(DWS) does not. The tables are deleted during migration.
SQL Server Syntax | Syntax After Migration |
|---|---|
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 supports the FILESTREAM keyword, which is used to specify the FILESTREAM data location of a table. GaussDB(DWS) does not support this keyword and is deleted during migration.
SQL Server Syntax | Syntax After Migration |
|---|---|
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 supports clustered and nonclustered indexes, but GaussDB(DWS) does not. The indexes are deleted during migration.
Primary key clustered indexes
SQL Server Syntax | Syntax After Migration |
|---|---|
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"); |
Unique index and nonclustered indexes
SQL Server Syntax | Syntax After Migration |
|---|---|
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"); |