Menu
AWS Schema Conversion Tool
User Guide (Version 1.0)

Microsoft SQL Server to MySQL Supported Schema Conversion

The following sections list the schema elements from Microsoft SQL Server and whether they are supported for automatic conversion to MySQL using the AWS Schema Conversion Tool.

Statements

SELECT

Clause Automatically Converted Details

WITH

No

Try converting the WITH(query) expression to a subquery and use it in a SELECT query.

UNION

Yes

EXCEPT | INTERSECT

No

ALL | DISTINCT

Yes

TOP

Partial

MySQL doesn't support TOP with the PERCENT or WITH TIES options. Try using the LIMIT option or perform a manual conversion.

<select_list>

Yes

INTO

No

FROM

Partial

MySQL doesn't support the CONTAINS and FREETEXT predicates in the FROM clause search condition.

WHERE

Yes

GROUP BY

Partial

MySQL doesn't support ORDER BY with the ROLLUP, CUBE, and GROUPING SETS options. Try creating a stored procedure to replace the query.

HAVING

Yes

ORDER BY

Partial

MySQL doesn't support ORDER BY with the OFFSET or FETCH options. Try creating a stored procedure to replace the query.

MySQL doesn't support ORDER BY with the COLLATE option. You must use the collation settings that were assigned when the database was created.

FOR

No

OPTION

No

INSERT

Clause Automatically Converted Details

WITH

No

Try to convert the WITH query to a subquery, and then use the subquery in the INSERT query.

INSERT

Yes

TOP

No

Get a count of all rows, and then use the following expression, where count_of_all_rows is your row count: percent_expression * count_of_all_rows / 100

INTO

Yes

table_name

Yes

WITH (<Table_Hint_Limited>)

No

Use MySQL methods of performance tuning.

OUTPUT

No

Create a trigger for INSERT statements for the table, and then save the inserted rows in a temporary table. After the INSERT operation, you can make use of the rows saved in the temporary table. This logic can be placed in a stored procedure.

VALUES

Yes

derived_table

Yes

execute_statement

No

Create a temporary table, fill it with the data to insert, and use the temporary table in the query.

<dml_table_source>

No

DEFAULT VALUES

No

UPDATE

Clause Automatically Converted Details

WITH

No

Try to convert the WITH query to a subquery, and then use the subquery in the UPDATE query.

TOP

Partial

MySQL doesn't support UPDATE with the PERCENT option. Get a count of all rows, and then use the following expression, where count_of_all_rows is your row count: percent_expression * count_of_all_rows / 100

WITH (<Table_Hint_Limited>)

No

Use MySQL methods of performance tuning.

SET

No

OUTPUT

No

Create a trigger for UPDATE statements for the table, and then save the changed rows in a temporary table. After the UPDATE operation, you can make use of the rows saved in the temporary table. This logic can be placed in a stored procedure.

FROM

Partial

MySQL doesn't support a FROM clause. AWS Schema Conversion Tool moves <table-source> to the UPDATE clause.

WHERE

Yes

CURRENT OF

No

Replace the CURRENT OF clause with an expression that consists of conditions that use unique key fields of the table.

GLOBAL

No

OPTION

No

Note

MySQL doesn't support FILESTREAM DATA. Perform a manual conversion to update the data in the file system file.

DELETE

Clause Automatically Converted Details

WITH

No

Try to convert the WITH query to a subquery, and then use the subquery in the DELETE query.

TOP

Partial

MySQL doesn't support the PERCENT option. A manual conversion is required. Get a count of all rows, and then use the following expression, where count_of_all_rows is your row count: percent_expression * count_of_all_rows / 100

FROM

Yes

table_or_view_name

Yes

rowset_function_limited

No

WITH

No

MySQL doesn't support hints in DELETE statements, so the AWS Schema Conversion Tool skips options in the format WITH(Table_Hint_Limited). Use MySQL methods of performance tuning.

OUTPUT

No

Create a trigger for DELETE statements for the table, and then save the deleted rows in a temporary table. After the DELETE operation, you can make use of the rows saved in the temporary table. This logic can be placed in a stored procedure.

WHERE

Yes

CURRENT OF

No

Replace the CURRENT OF clause with an expression that consists of conditions that use unique key fields of the table.

GLOBAL

No

OPTION

No

Procedures

CREATE PROCEDURE

Clause Automatically Converted Details

@parameter

Yes

MySQL doesn't support procedure arguments of the CURSOR data type. Change the business logic to eliminate the need to send cursors through arguments to a stored procedure.

VARYING

Partial

OUT

OUTPUT

Partial

READONLY

Partial

WITH

No

FOR REPLICATION

No

AS BEGIN … END

No

ENCRYPTION

Yes

RECOMPILE

No

EXECUTE AS

No

Flow Control

Clause Automatically Converted Details

TRY…CATCH

THROW

No

Try using the DECLARE HANDLER for the CATCH emulation block and the SIGNAL SQLSTATE operator to emulate the THROW operator.

WAITFOR

No

Perform a manual conversion.

DECLARE

No

Perform a manual conversion.

GOTO

No

Revise your code to eliminate GOTO operators, using BEGIN...END blocks in combination with LEAVE, REPEAT, UNTIL, and WHILE operators.

BREAK

Yes

CONTINUE

Yes

IF…ELSE

Yes

RETURN

No

MySQL does not support returning a value from a procedure using the RETURN statement. To return a value, use the OUT parameter or a result set.

WHILE

Yes

CASE

Yes

COALESCE

Yes

NULLIF

Yes

Functions

In this section, you can find a list of the Microsoft SQL Server built-in functions that indicates whether the AWS Schema Conversion Tool performs an automatic conversion. Where MySQL doesn't support a function, consider creating a user-defined function.

Aggregate Functions

Function Automatically Converted Details

AVG

Yes

COUNT

Yes

COUNT_BIG

Partial

MIN

Yes

MAX

Yes

SUM

Yes

CHECKSUM_AGG

No

STDEV

Partial

MySQL doesn't support the STDEV function with the DISTINCT clause.

STDEVP

Partial

MySQL doesn't support the STDEVP function with the DISTINCT clause.

GROUPING

No

GROUPING_ID

No

VAR

Partial

MySQL doesn't support the VAR function with the DISTINCT clause.

VARP

Partial

MySQL doesn't support the VARP function with the DISTINCT clause.

Date and Time Functions

Function Automatically Converted Details

DATEADD

Partial

MySQL doesn't support the DATEADD function with the nanosecond datepart.

DATEDIFF

Partial

MySQL doesn't support the DATEDIFF function with the week, millisecond, or nanosecond datepart.

DATENAME

Partial

MySQL doesn't support the DATENAME function with the millisecond, nanosecond, or TZoffset datepart.

DATEPART

Partial

MySQL doesn't support the DATEPART function with the millisecond, nanosecond, or TZoffset datepart.

DAY

Partial

GETDATE

Partial

GETDATE + 1

Partial

GETUTCDATE

Partial

MONTH

Partial

YEAR

Partial

DATETIMEOFFSETFROMPARTS

No

ISDATE

No

SWITCHOFFSET

No

SYSDATETIMEOFFSET

No

TODATETIMEOFFSET

No

Mathematical Functions

Function Automatically Converted Details

ABS

Yes

ACOS

Yes

ASIN

Yes

ATN2

Partial

ATAN

Yes

CEILING

Yes

COS

Yes

COT

Yes

DEGREES

Yes

EXP

Yes

FLOOR

Yes

LOG10

Yes

LOG

Yes

PI

Yes

POWER

Yes

RADIANS

Yes

RAND

Yes

ROUND

Partial

MySQL doesn't support the ROUND function with the function argument.

SIGN

Yes

SIN

Yes

SQRT

Yes

SQUARE

No

TAN

Yes

String Functions

Function Automatically Converted Details

ASCII

Yes

CHAR

Yes

CHARINDEX

No

CONCAT

Yes

DIFFERENCE

No

FORMAT

No

LEFT

Yes

LEN

Partial

LOWER

Yes

LTRIM

Yes

NCHAR

No

PATINDEX

No

QUOTENAME

Partial

REPLACE

Yes

REPLICATE

Partial

REVERSE

Yes

RIGHT

Yes

RTRIM

Yes

SOUNDEX

No

SPACE

Yes

STR

No

STUFF

No

SUBSTRING

Yes

UNICODE

No

UPPER

Yes

Configuration Functions

Function Automatically Converted Details

@@DATEFIRST

No

@@DBTS

Yes

@@LANGID

Yes

@@LANGUAGE

No

@@LOCK_TIMEOUT

Yes

@@MAX_CONNECTIONS

No

@@MAX_PRECISION

Yes

@@NESTLEVEL

Yes

@@OPTIONS

Yes

@@REMSERVER

Yes

@@SERVERNAME

No

@@SERVICENAME

Yes

@@SPID

Yes

@@TEXTSIZE

Yes

@@VERSION

No

Conversion Functions

Function Automatically Converted Details

CAST and CONVERT

No

PARSE

No

TRY_CAST

No

TRY_CONVERT

No

TRY_PARSE

No

Security Functions

Function Automatically Converted Details

CERTENCODED

Yes

CERTPRIVATEKEY

Yes

CURRENT_USER

No

DATABASE_PRINCIPAL_ID

No

HAS_PERMS_BY_NAME

Yes

IS_MEMBER

Yes

IS_ROLEMEMBER

Yes

IS_SRVROLEMEMBER

Yes

ORIGINAL_LOGIN

No

PERMISSIONS

Yes

PWDCOMPARE

Yes

PWDENCRYPT

Yes

SCHEMA_ID

Yes

SCHEMA_NAME

No

SESSION_USER

No

SUSER_ID

Yes

SUSER_NAME

Yes

SUSER_SID

Yes

SUSER_SNAME

Yes

sys.fn_builtin_permissions

Yes

sys.fn_get_audit_file

Yes

sys.fn_my_permissions

Yes

SYSTEM_USER

Yes

USER_ID

Yes

USER_NAME

Yes

System Functions

Function Automatically Converted Details

$PARTITION

Yes

@@ERROR

Yes

@@IDENTITY

Yes

@@PACK_RECEIVED

Yes

@@ROWCOUNT

Yes

@@TRANCOUNT

Yes

BINARY_CHECKSUM

Yes

CHECKSUM

No

CONNECTIONPROPERTY

Yes

CONTEXT_INFO

Yes

CURRENT_REQUEST_ID

Yes

ERROR_LINE

No

ERROR_MESSAGE

No

ERROR_NUMBER

No

ERROR_PROCEDURE

No

ERROR_SEVERITY

No

ERROR_STATE

No

FORMATMESSAGE

No

GET_FILESTREAM_TRANSACTION_CONTEXT

Yes

GETANSINULL

Yes

HOST_ID

No

HOST_NAME

No

ISNULL

No

MIN_ACTIVE_ROWVERSION

Yes

NEWID

Yes

NEWSEQUENTIALID

Yes

PARSENAME

Yes

ROWCOUNT_BIG

Yes

XACT_STATE

Yes

Logical Functions

Function Automatically Converted Details

CHOOSE

No

IIF

No

CREATE FUNCTION

Clause Automatically Converted Details

@parameter_name

Yes

type_schema_name

Yes

parameter_data_type

No

= default

Yes

READONLY

No

RETURNS return_data_type

No

WITH function_option

Yes

BEGIN … END

No

RETURN scalar_expression

Yes

MySQL supports only functions that return a scalar value.

EXECUTE

Clause Automatically Converted Details

@parameter_name

Yes

type_schema_name

Yes

parameter_data_type

No

= default

Yes

READONLY

No

RETURNS return_data_type

No

WITH function_option

Yes

BEGIN … END

No

RETURN scalar_expression

Yes

MySQL supports only functions that return a scalar value.

Operators

Arithmetic Operators

Clause Automatically Converted Details

+

Yes

+=

Yes

-

Yes

-=

Yes

*

Yes

*=

Yes

/

Yes

/=

Yes

%

Yes

Assignment Operator

Clause Automatically Converted Details

=

Yes

Bitwise Operators

Clause Automatically Converted Details

&

Yes

|

Yes

^

Yes

~

Yes

Comparison Operators

Clause Automatically Converted Details

=

Yes

>

Yes

<

Yes

>=

Yes

<=

Yes

<>

Yes

!=

Yes

!<

Yes

!>

Yes

Logical Operators

Clause Automatically Converted Details

ALL

Yes

AND

Yes

ANY

Yes

BETWEEN

Yes

EXISTS

Yes

IN

Yes

LIKE

Yes

NOT

Yes

OR

Yes

SOME

Partial

The AWS Schema Conversion Tool converts SOME to ANY.

Set Operators

Clause Automatically Converted Details

UNION

Yes

UNION ALL

Yes

EXCEPT

No

Perform a manual conversion.

INTERSECT

No

Perform a manual conversion.

String Concatenation Operator

Clause Automatically Converted Details

+

Yes

Unary Operators

Clause Automatically Converted Details

+

Yes

-

Yes

Transactions

BEGIN TRANSACTION

Clause Automatically Converted Details

DISTRIBUTED

No

MySQL doesn't support distributed transactions. Revise your architecture so that it doesn't use distributed transactions.

transaction_name

No

MySQL doesn't support named transactions. Revise your code to eliminate marked transactions.

WITH MARK

No

MySQL doesn't support the WITH MARK option. Revise your code to eliminate marked transactions.

SAVE

Partial

ROLLBACK

Partial

transaction_name

No

MySQL doesn't support named transactions. Revise your code to eliminate named transactions.

savepoint_name

Partial

COMMIT

Partial

DELAYED_DURABILITY

No

Data Types

Numerics

Data type Automatically Converted Default Conversion Details

bigint

Yes

bigint

int

Yes

int

smallint

Yes

smallint

tinyint

Yes

tinyint unsigned

bit

Yes

bit(1)

money

Yes

numeric(19,4)

smallmoney

Yes

numeric(10,4)

numeric

Yes

numeric

decimal

Yes

decimal

float

Yes

double

real

Yes

float

Date and Time

Data type Automatically Converted Default Conversion Details

date

Partial

date

0001-01-01 through 9999-12-31

datetime2(7)

Partial

datetime

0001-01-01 through 9999-12-31 00:00:00 through 23:59:59.9999999

datetime

Partial

datetime

1753-01-01, through 9999-12-31 00:00:00 through 23:59:59.997

datetimeoffset(7)

No

smalldatetime

Yes

datetime

time(7)

Yes

time

Character Strings

Data type Automatically Converted Default Conversion Details

char(len)

Partial

char

len= 1 - 255 (2^8 - 1) symbols (loss range)

varchar(len)

Yes

varchar

varchar(max)

Yes

longtext

text

Yes

longtext

nchar(len)

len = 1 - 4000

char

len= 1 - 255 (2^8 - 1) symbols (loss range)

nvarchar(len)

Yes

varchar

nvarchar(max)

Yes

longtext

ntext

Yes

longtext

Binary Strings

Data type Automatically Converted Default Conversion Details

binary(len)

Yes

blob

varbinary(len)

Yes

blob

varbinary(max)

Yes

longblob

hierarchyid

No

sql_variant

No

table

No

uniqueidentifier

No

xml

No

geography

No

geometry

No

Special Types

Data type Automatically Converted Default Conversion Details

UNIQUEIDENTIFIER

Partial

CHAR(38) OR BINARY(16)

DOUBLE PRECISION

Partial

FLOAT(53)

IDENTITY

Partial

AUTO_INCREMENT

SYSNAME

Partial

NVARCHAR(128) NOT NULL

Data Definition Language (DDL)

CREATE TABLE

CREATE TABLE statements are converted automatically except for the following clause.

Clause Automatically Converted Details

SET DEFAULT

No

MySQL doesn't support the SET DEFAULT option for FOREIGN KEY.

CREATE INDEX

The AWS Schema Conversion Tool does not support automatic migration of data definition language (DDL) code to create an index.

CREATE TRIGGER

CREATE TRIGGER statements are converted automatically except for the following clause.

Clause Automatically Converted Details

FOR

No

MySQL doesn't support a FOR clause in a CREATE TRIGGER statement.

CREATE VIEW

CREATE VIEW statements are converted automatically except for the following clauses.

Clause Automatically Converted Details

SCHEMABINDING

No

AWS Schema Conversion Tool ignores this clause.

ENCRYPTION

No

AWS Schema Conversion Tool ignores this clause.

VIEW_METADATA

No

AWS Schema Conversion Tool ignores this clause.

Cursors

DECLARE CURSOR

Clause Automatically Converted Details

LOCAL

Yes

Change the global cursor to a local cursor, or revise your code so it doesn't require global cursors.

GLOBAL

No

Setting this option corresponds to the typical behavior of cursors in MySQL, so the AWS Schema Conversion Tool skips this option during conversion.

FORWARD_ONLY

Partial

Revise your code to eliminate cursors with the SCROLL option.

SCROLL

No

STATIC

Yes

The membership and order of rows never changes for cursors in MySQL, so the AWS Schema Conversion Tool skips this option during conversion. Verify that the converted schema behavior is acceptable.

KEYSET

Partial

Revise your code to eliminate dynamic cursors.

DYNAMIC

No

Setting this option corresponds to the typical behavior of cursors in MySQL, so the AWS Schema Conversion Tool skips this option during conversion.

FAST_FORWARD

Yes

All MySQL cursors are read-only, so the AWS Schema Conversion Tool skips this option during conversion.

READ_ONLY

Yes

MySQL doesn't support the option SCROLL_LOCKS, so the AWS Schema Conversion Tool ignores this option during conversion. Verify that the converted schema behavior is acceptable.

SCROLL_LOCKS

Partial

MySQL doesn't support the option OPTIMISTIC, so the AWS Schema Conversion Tool ignores this option during conversion. Verify that the converted schema behavior is acceptable.

OPTIMISTIC

Partial

MySQL doesn't support the option TYPE_WARNING, so the AWS Schema Conversion Tool ignores this option during conversion. Verify that the converted schema behavior is acceptable.

TYPE_WARNING

Partial

Change the global cursor to a local cursor, or revise your code so it doesn't require global cursors.

Related Topics