Help Center/ GaussDB(DWS)/ Tool Guide/ DSC/ SQL-Server Syntax Migration/ Migrating Tables
Updated on 2025-01-24 GMT+08:00
View PDF
Share

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");