Editing the Configurations During Migration from Microsoft SQL Server to GaussDB
Symptom |
Feature Name |
Recommended Setting |
---|---|---|
Microsoft SQL Server's table fields support the IDENTITY attribute, but GaussDB's table fields do not support this attribute. By default, migration cannot be performed. |
IDENTITY columns |
Convert IDENTITY columns to the SERIAL types. Impact: The sequence number data type is a four-byte auto-increment integer. Its range is 1 to 2,147,483,647. If the upper limit or lower limit is exceeded, an error will be reported and DML statements fail to be executed. |
Microsoft SQL Server's table fields support the BIT attribute, but GaussDB's table fields do not support this attribute. By default, migration cannot be performed. |
BIT types |
Convert BIT types to BOOLEAN types. Impact: Compared with the value of the BIT type, the value of the BOOLEAN type has one more null. |
Microsoft SQL Server supports DateTimeOffset types, but GaussDB does not. By default, DateTimeOffset is converted to TIMESTAMPTZ (The precision of the maximum seconds is different). The processing results of the TIME function, such as YEAR, are different in special cases. |
DateTimeOffset |
Convert DateTimeOffset to VARCHAR2. Impact: The DateTimeOffset type is converted to the VARCHAR2 type. |
In GaussDB O mode, an empty string ('') is equivalent to null. Function conversion solutions such as ISNULL have semantic problems. |
Ignore the risks that the empty character string ('') is converted to NULL |
Do not ignore the risks that the empty character string ('') is converted to NULL. Impact: Functions that have empty character strings ('') are not converted. |
Distributed GaussDB does not support foreign key constraints. If CREATE TABLE and ALTER TABLE statements involve foreign key constraints, execution exceptions may occur. You need to determine whether to use the foreign key features based on service requirements. |
FOREIGN KEY constraints |
Impact: After the FOREIGN KEY constraints are commented, the foreign key constraints of the original tables become invalid. As a result, data consistency cannot be ensured and inconsistent data may be created. |
GaussDB does not support Microsoft SQL Server's table variables (including table definitions returned by multi-statement table-valued functions), so GaussDB can convert the table variables to temporary tables. They are different in some application scenarios. You can determine whether to convert table variables as required. |
Table variable conversion (including table definitions returned by multi-statement table-valued functions) |
Convert table variables to temporary tables. Impact: In Microsoft SQL Server, explicit table variables do not comply with the rollback semantics of transactions. In GaussDB, temporary tables comply with the rollback semantics of transactions. You need to check whether the service semantics changes. |
GaussDB does not support SELECT statements in stored procedures. Currently, GaussDB uses dynamic cursors to transfer results from stored procedures. |
Whether SELECT statements in stored procedures should be handled using cursors |
Use cursors to process SELECT statements in stored procedures. Impact: If you need to get the result set in a stored procedure, you need to modify the associated call form. |
DML operations are not blocked during index creation. |
CONCURRENTLY clause in CREATE INDEX statement |
Recommended value: Add the keyword CONCURRENTLY. Impact: When such indexes are created, there may be deadlocks. It takes longer to create an Astore table index than a normal table index. |
GaussDB reserves the case of object names and uses double quotation marks to quote object names. |
Object names case format |
Convert the case of all object names to lowercase letters. |
In Microsoft SQL Server, if the data types in the expressions on both sides of the LIKE operator must be character strings plus a plus sign (+), but it can be regarded as risky character string concatenation in GaussDB. |
Whether to treat the plu sign (+) within expressions on both sides of the LIKE operator as string |
Treat the + sign within expressions on both sides of the LIKE operator as string Impact: In some cases, the plus sign (+) that originally represents arithmetic operations might have been incorrectly converted to the string concatenation operator. |
The name of an index and constraint in GaussDB must be globally unique and cannot exceed 63 bytes. |
Prevent index/constraint names collision |
Recommended value: Concat the table name with the index or constraint name to prevent name conflicts. If the concatenated name exceeds 63 bytes, perform a hash on the name using SHA-1. Impact: The name conflict problem is solved. However, when the name is too long, the readability of the name is lost. In addition, in the subsequent maintenance processes such as DROP and RENAME, you need to use UGO to convert the SQL statement so that the name can be correctly executed. |
The UPDATE(column) function used in Microsoft SQL Server triggers does not have a perfect equivalence in GaussDB. |
UPDATE(column) syntax |
Recommended value: Convert UPDATE(column) to case TG_OP when 'UPDATE' then NEW.column <> OLD.column when 'INSERT' then true else false end. Impact: This configuration is different from that in Microsoft SQL Server when the column value remains unchanged after the column is updated. When the column value remains unchanged before and after the update, the result of the UPDATE(column) condition in Microsoft SQLServer is True, but the result of the (NEW.column <> OLD.column) condition in GaussDB is False. |
In distributed GaussDB, distribution keys are part of the unique indexes. |
Unique indexes in a distributed (multi data nodes) environment
NOTE:
This feature is available only in distributed GaussDB. |
Add the distribution key to unique indexes if the distribution key is not present in them. Impact: Since Distribution key should be part of the unique index if the target db is distributed environment. So this will add the distribution key as a column in the unique index. |
In SQL Server, OUT/OUTPUT indicates a stored procedure parameter may return a value; in GaussDB, such parameters invariably do so. |
Whether to convert the OUT/OUTPUT mode of stored procedure parameters |
Adjust OUT/OUTPUT parameter mode to INOUT. Impact: In SQL Server, OUT/OUTPUT indicates a stored procedure parameter may return a value; in GaussDB, such parameters invariably do so. |
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot