Data types for ANSI SQL - SQL Server to Aurora MySQL Migration Playbook
SQL Server UsageMySQL UsageSummary

Data types for ANSI SQL

This topic provides reference content about data type compatibility when migrating from Microsoft SQL Server 2019 to Amazon Aurora MySQL. You can use this information to understand how different data types in SQL Server map to their counterparts in Aurora MySQL.

Feature compatibility AWS SCT / AWS DMS automation level AWS SCT action code index Key differences

Four star feature compatibility

Four star automation level

Data Types

Minor syntax and handling differences. No special UNICODE data types.

SQL Server Usage

In SQL Server, each table column, variable, expression, and parameter has an associated data type.

SQL Server provides a rich set of built-in data types as summarized in the following table.

Category Data Types

Numeric

BIT, TINYINT, SMALLINT, INT, BIGINT, NUMERIC, DECIMAL, MONEY, SMALLMONEY, FLOAT, REAL

String and character

CHAR, VARCHAR, NCHAR, NVARCHAR

Temporal

DATE, TIME, SMALLDATETIME, DATETIME, DATETIME2, DATETIMEOFFSET

Binary

BINARY, VARBINARY

Large Object (LOB)

TEXT, NTEXT, IMAGE, VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX)

Cursor

CURSOR

GUID

UNIQUEIDENTIFIER

Hierarchical identifier

HIERARCHYID

Spatial

GEOMETRY, GEOGRAPHY

Sets (table type)

TABLE

XML

XML

Other specialty types

ROW VERSION, SQL_VARIANT

Note

You can create custom user-defined data types using T-SQL, and the .NET framework. Custom data types are based on the built-in system data types and are used to simplify development. For more information, see User-Defined Types.

TEXT, NTEXT, and IMAGE Deprecated Data Types

The TEXT, NTEXT, and IMAGE data types have been deprecated as of SQL Server 2008 R2. For more information, see Deprecated Database Engine Features in SQL Server 2008 R2 in the SQL Server documentation.

These data types are legacy types for storing BLOB and CLOB data. The TEXT data type was used to store ASCII text CLOBS, the NTEXT data type to store UNICODE CLOBS, and IMAGE was used as a generic data type for storing all BLOB data. In SQL Server 2005, Microsoft introduced the new and improved VARCHAR (MAX), NVARCHAR(MAX), and VARBINARY(MAX) data types as the new BLOB and CLOB standard. These new types support a wider range of functions and operations. They also provide enhanced performance over the legacy types.

If your code uses TEXT, NTEXT, or IMAGE data types, AWS SCT automatically converts them to the appropriate Amazon Aurora MySQL-Compatible Edition (Aurora MySQL) BLOB data type. TEXT and NTEXT are converted to LONGTEXT and image to LONGBLOB. Make sure you use the proper collations. For more details, see the Collations.

Examples

Define table columns.

CREATE TABLE MyTable
(
    Col1 AS INTEGER NOT NULL PRIMARY KEY,
    Col2 AS NVARCHAR(100) NOT NULL
);

Define variable types.

DECLARE @MyXMLType AS XML,
    @MyTemporalType AS DATETIME2
DECLARE @MyTableType
AS TABLE
(
    Col1 AS BINARY(16) NOT NULL PRIMARY KEY,
    Col2 AS XML NULL
);

For more information, see Data types (Transact-SQL) in the SQL Server documentation.

MySQL Usage

Amazon Aurora MySQL-Compatible Edition (Aurora MySQL) supports the following data types:

Category Data Types

Numeric

BIT, INTEGER, SMALLINT, TINYINT, MEDIUMINT, BIGINT, DECIMAL, NUMERIC, FLOAT, DOUBLE

String and character

CHAR, VARCHAR, SET

Temporal

DATE, DATETIME, TIMESTAMP, TIME, YEAR

Binary

BINARY, VARBINARY

Large Object (LOB)

BLOB, TEXT

Cursor

CURSOR

Spatial

GEOMETRY, POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION

JSON

JSON

Be aware that Aurora MySQL uses different rules than SQL Server for handling out-of-range and overflow situations. SQL Server always raises an error for out-of-range values. Aurora MySQL exhibits different behavior depending on run time settings.

For example, a value may be clipped to the first or last value in the range of permitted values for the data type if STRICT SQL mode isn’t set.

For more information, see Out-of-Range and Overflow Handling in the MySQL documentation.

Converting from TEXT, NTEXT, and IMAGE SQL Server Deprecated Data Types

The legacy SQL Server types for storing LOB data are deprecated as of SQL Server 2008 R2.

When you convert from these types to Aurora MySQL using the AWS Schema Conversion Tool (AWS SCT, they are converted as shown following:

SQL Server LOB Type Converted to Aurora MySQL data type Comments

TEXT

LONGTEXT

Make sure to choose the right collation. For more information, see Collations.

NTEXT

LONGTEXT

Make sure to choose the right collation. For more information, see Collations.

IMAGE

LONGBLOB

The size cap for all of these types is compatible and is capped at 2 GB of data, which may allow less characters depending on the chosen collation.

Note

Aurora MySQL supports UCS-2 collation, which is compatible with SQL Server UNICODE types.

While it is safe to use the default conversion types, remember that, unlike SQL Server, Aurora MySQL also provides smaller BLOB and CLOB types, which may be more efficient for your data.

Even the basic VARCHAR and VARBINARY data types can store strings up to 32 KB, which is much longer than SQL Server 8 KB limit. If the strings or binary data that you need to store don’t exceed 32 KB, it may be more efficient to store these as non-LOB types in Aurora MySQL.

Summary

The following table summarizes the key differences and migration considerations for migrating from SQL Server data types to Aurora MySQL data types.

SQL Server Data Type Convert to MySQL Data Type Comments

BIT

BIT

Aurora MySQL also supports BIT(m), which can be used to store multiple bit values. In SQL Server, literal bit notation uses the numerical digits 0 and 1. Aurora MySQL uses b'<value> or 0b<value> notations.

For more information, see Bit-Value Type - BIT and Bit-Value Literals in the MySQL documentation.

TINYINT

TINYINT

SQL Server only supports unsigned TINYINT, which can store values between 0 and 255. Aurora MySQL supports both signed TINYINT and TINYINT UNSIGNED. The latter can be used to store values between -128 and 127. The default for integer types in Aurora MySQL is to use signed integers. For compatibility, explicitly specify TINYINT UNSIGNED.

For more information, see Integer Types (Exact Value) in the MySQL documentation.

SMALLINT

SMALLINT

Compatible type. SQL Server supports only signed SMALLINT. Aurora MySQL also supports SMALLINT UNSIGNED, which can store values between 0 and 65535. The default for integer types in Aurora MySQL is to use signed integers. Consider using unsigned integers for storage optimization.

For more information, see Integer Types (Exact Value) in the MySQL documentation.

INTEGER

INTEGER

Compatible type. SQL Server supports only signed INTEGER, which can store values between -2147483648 and 2147483647. Aurora MySQL also supports INTEGER UNSIGNED, which can store values between 0 and 4294967295. The default for integer types in Aurora MySQL is to use signed integers. Consider using unsigned integers for storage optimization.

Aurora MySQL also supports a MEDIUMINT type, which uses only three bytes of storage vs. four bytes for INTEGER. For large tables, consider using MEDIUMINT instead of INT if the value range is within -8388608 to -8388607 for a SIGNED type, or 0 to 16777215 for UNSIGNED type.

For more information, see Integer Types (Exact Value) in the MySQL documentation.

BIGINT

BIGINT

Compatible type. SQL Server supports only signed BIGINT. Aurora MySQL also supports BIGINT UNSIGNED, which can store values between 0 and 2^64-1. The default for integer types in Aurora MySQL is to use signed integers. Consider using unsigned integers for storage optimization.

For more information, see Integer Types (Exact Value) in the MySQL documentation.

NUMERIC / DECIMAL

NUMERIC / DECIMAL

Compatible types. DECIMAL and NUMERIC are synonymous.

MONEY / SMALLMONEY

N/A

Aurora MySQL doesn’t support dedicated monetary types. Use NUMERIC / DECIMAL instead. If your application uses literals with monetary signs (for example, $50.23), rewrite to remove the monetary sign.

FLOAT / REAL

FLOAT / REAL / DOUBLE

Compatible types. In SQL Server, both REAL and FLOAT(n), where n⇐24, use 4 bytes of storage, are equivalent to FLOAT and REAL in Aurora MySQL. In SQL Server, FLOAT(n), where n>24, uses 8 bytes.

The Aurora MySQL DOUBLE PRECISION type always uses 8 bytes.

Aurora MySQL also supports the nonstandard FLOAT(M,D), REAL(M,D) or DOUBLE PRECISION(M,D) where (M,D) indicates values can be stored with up to M digits in total with D digits after the decimal point.

For more information, see Floating-Point Types (Approximate Value) in the MySQL documentation.

CHAR

CHAR / VARCHAR

Compatible types up to 255 characters only. SQL Server supports CHAR data types up to 8,000 characters. The Aurora MySQL CHAR data type is limited to a maximum of 255 characters.

For strings requiring more than 255 characters, use VARCHAR. When converting from CHAR to VARCHAR, exercise caution because VARCHAR behaves differently than CHAR; trailing spaces are trimmed.

For more information, see The CHAR and VARCHAR Types in the MySQL documentation.

VARCHAR

VARCHAR

Compatible types. SQL Server supports VARCHAR columns up to 8,000 characters. Aurora MySQL can store up to 65,535 characters with regard to the maximal row size limit.

For more information, see The CHAR and VARCHAR Types in the MySQL documentation.

NCHAR

CHAR

Aurora MySQL doesn’t require the use of specific data types for storing UNICODE data. Use the CHAR data type and define a UNICODE collation using the CHARACTER SET or COLLATE keywords.

For more information, see Unicode Character Sets in the MySQL documentation.

NVARCHAR

VARCHAR

Aurora MySQL doesn’t require the use of specific data types for storing UNICODE data. Use the VARCHAR data type and define a UNICODE collation using the CHARACTER SET or COLLATE keywords.

For more information, see Unicode Character Sets in the MySQL documentation.

DATE

DATE

Compatible types. The range for SQL Server DATE data type is '0001-01-01' through '9999-12-31'. The range for Aurora MySQL is '1000-01-01' through '9999-12-31'.

Aurora MySQL doesn’t support dates before 1000 AD. For more information, see Date and Time Functions.

For more information, see The DATE, DATETIME, and TIMESTAMP Types in the MySQL documentation.

TIME

TIME

Compatible types. SQL Server supports explicit fractional seconds using the format TIME(n) where n is between 0 to 7. Aurora MySQL doesn’t allow explicit fractional setting.

Aurora MySQL supports up to 6 digits for microsecond resolution of fractional seconds. SQL Server provides one more digit to support a resolution of up to 100 nanoseconds.

If your application uses the TIME(n) format, rewrite to remove the (n) setting.

Aurora MySQL also supports TIME values that range from -838:59:59 to 838:59:59. You can use the hours part to represent the time of day, where hours must be less than 24, or to represent a time interval, which can be greater than 24 hours and have negative values.

For more information, see The TIME Type in the MySQL documentation.

SMALLDATETIME

DATETIME / TIMESTAMP

Aurora MySQL doesn’t support SMALLDATETIME. Use DATETIME instead. Use SMALLDATETIME for storage space optimization where lower ranges and resolutions are required.

For more information, see Date and Time Functions.

DATETIME

DATETIME

In SQL Server, the DATETIME data type supports a value range between 1753-01-01 and 9999-12-31 with a resolution of up to 3.33ms. Aurora MySQL DATETIME supports a wider value range between 1000-01-01 00:00:00 and 9999-12-31 23:59:59 with a higher resolution of microseconds and optional six fractional second digits.

For more information, see Date and Time Functions.

For more information about DATETIME, see The DATE, DATETIME, and TIMESTAMP Types in the MySQL documentation.

DATETIME2

DATETIME

In SQL Server, the DATETIME2 data type supports a value range between 0001-01-01 and 9999-12-31 with a resolution of up to 100 nanoseconds using seven fractional second digits. Aurora MySQL DATETIME supports a narrower value range between 1000-01-01 00:00:00 and 9999-12-31 23:59:59 with a lower resolution of microseconds and optional six fractional second digits.

For more information, see Date and Time Functions.

For more information about DATETIME, see The DATE, DATETIME, and TIMESTAMP Types in the MySQL documentation.

DATETIMEOFFSET

TIMESTAMP

Aurora MySQL doesn’t support full time zone awareness and management functions. Use the time_zone system variable in conjunction with TIMESTAMP columns to achieve partial time zone awareness.

For more information, see Server Options.

In Aurora MySQL, TIMESTAMP isn’t the same as in SQL Server. The latter is a synonym for ROW_VERSION. Aurora MySQL TIMESTAMP is equivalent to the DATETIME type with a smaller range.

With Aurora MySQL DATETIME, you can use values between 1000-01-01 00:00:00 and 9999-12-31 23:59:59. TIMESTAMP is limited to values between 1970-01-01 00:00:01 and 2038-01-19 03:14:07.

Aurora MySQL converts TIMESTAMP values from the current time zone to UTC for storage and back from UTC to the current time zone for retrieval.

For more information, see MySQL Server Time Zone Support in the MySQL documentation.

BINARY

BINARY / VARBINARY

In Aurora MySQL, the BINARY data type is considered to be a string data type and is similar to CHAR. BINARY contains byte strings rather than character strings and uses the binary character set and collation. Comparison and sorting are based on the numeric values of the bytes in the values.

SQL Server supports up to 8,000 bytes for a BINARY data types. Aurora MySQL BINARY is limited to 255 characters, similar to CHAR. If larger values are needed, use VARBINARY.

Literal assignment for Aurora MySQL BINARY types use string literals, unlike SQL Server explicit binary 0x notation.

For more information, see The BINARY and VARBINARY Types and The binary Collation Compared to bin Collations in the MySQL documentation.

VARBINARY

VARBINARY

In Aurora MySQL, the VARBINARY data type is considered a string data type, similar to VARCHAR. VARBINARY contains byte strings rather than character strings and has a binary character set. Collation, comparison, and sorting are based on the numeric values of the bytes in the values.

Aurora MySQL VARBINARY supports up to 65,535 characters, significantly larger than the 8,000 byte limit in SQL Server. Literal assignment for Aurora MySQL BINARY types use string literals, unlike SQL Server explicit binary 0x notation.

For more information, see The BINARY and VARBINARY Types and The binary Collation Compared to bin Collations in the MySQL documentation.

TEXT / VARCHAR (MAX)

VARCHAR / TEXT / MEDIUMTEXT / LONGTEXT

In SQL Server, a TEXT data type is a variable-length ASCII string data type with a maximum string length of 2^31-1 (2 GB).

Use the following list to determine the optimal Aurora MySQL data type:

  • For a string length of 2^16-1 bytes, use VARCHAR or TEXT.

  • For a string length of 2^24-1 bytes, use MEDIUMTEXT.

  • For a string length of 2^32-1 bytes, use LONGTEXT.

For more information, see The BLOB and TEXT Types and Data Type Storage Requirements in the MySQL documentation.

NTEXT / NVARCHAR (MAX)

VARCHAR / TEXT / MEDIUMTEXT / LONGTEXT

Aurora MySQL doesn’t require the use of specific data types for storing UNICODE data. Use the TEXT compatible data types listed earlier and define a UNICODE collation using the CHARACTER SET or COLLATE keywords.

For more information, see Unicode Character Sets in the MySQL documentation.

IMAGE / VARBINARY (MAX)

VARBINARY / BLOB / MEDIUMBLOB / LONGBLOB

In SQL Server, an IMAGE data type is a variable-length binary data type with a range of 0 through 2^31-1 (2 GB).

Similar to the BINARY and VARBINARY data types, the BLOB data types are considered string data types. BLOB data types contain byte strings rather than character strings and use a binary character set. Collation, comparison, and sorting are based on the numeric values of the bytes in the values.

Use the following list to determine the optimal Aurora MySQL data type:

  • For a string length of 2^16-1 bytes, use VARBINARY or BLOB.

  • For a string length of 2^24-1 bytes, use MEDIUMBLOB.

  • For a string length of 2^32-1 bytes, use LONGBLOB.

For more information, see The BLOB and TEXT Types, String Type Storage Requirements, and The binary Collation Compared to bin Collations in the MySQL documentation.

CURSOR

CURSOR

Types are compatible, although in Aurora MySQL a cursor isn’t really considered to be a type. For more information, see Cursors.

UNIQUEIDENTIFIER

N/A

Aurora MySQL doesn’t support a native unique identifier type. Use BINARY(16), which is the same base type used for the UNIQUEIDENTIFIER type in SQL Server. It generates values using the UUID() function, which is the equivalent of the NEW_ID function in SQL Server.

UUID returns a Universal Unique Identifier generated according to RFC 4122. For more information, see A Universally Unique IDentifier (UUID) URN Namespace.

For more information, see UUID() in the MySQL documentation.

HIERARCHYID

N/A

Aurora MySQL doesn’t support native hierarchy representation. Rewrite functionality with custom application code using one the common SQL hierarchical data representation approaches:

  • Adjacency list.

  • Nested set.

  • Closure table.

  • Materialized path.

For more information, see Adjacency list and Nested set model.

GEOMETRY

GEOMETRY

In SQL Server, the GEOMETRY type represents data in a Euclidean (flat) coordinate system. SQL Server supports a set of methods for this type, which include methods defined by the Open Geospatial Consortium (OGC) standard, and a set of additional extensions.

Aurora MySQL supports GEOMETRY spatial data, although the syntax and functionality may differ significantly from SQL Server. A rewrite of the code is required.

For more information, see Spatial Data Types in the MySQL documentation.

TABLE

N/A

Aurora MySQL doesn’t support a TABLE data type. For more information, see User-Defined Types.

XML

N/A

Aurora MySQL doesn’t support a native XML data type. However, it does provide full support for JSON data types, which SQL Server doesn’t.

Because XML and JSON are text documents, consider migrating the XML formatted documents to JSON or use string BLOBs and custom code to parse and query documents.

For more information, see The JSON Data Type in the MySQL documentation.

ROW_VERSION

N/A

Aurora MySQL doesn’t support a row version. Use triggers to update a dedicated column from a primary sequence value table.

SQL_VARIANT

N/A

Aurora MySQL doesn’t support a hybrid, all-purpose data type similar to SQL_VARIANT in SQL Server. Rewrite applications to use explicit types.

For more information, see Data Types in the MySQL documentation.