Mapping MySQL and SQL Server Data Types

MS SQL Server and MySQL use similar data types, but have some important differences. In order to get correct data after migration you have to do some mapping.

This article describes the most important data types in MySQL and their MS SQL Server equivalents.

Integer data types

MySQL MS SQL Server Mapping notes
TINYINT(1) bit -
TINYINT tinyint MS SQL tinyint is always unsigned.
SMALLINT smallint -
MEDIUMINT, INT int int takes up 4 bytes
BIGINT bigint -

If not unsigned, the MySQL TINYINT datatype can range from -127 to 127; whereas the SQL Server TINYINT type always ranges 0 to 255. So, unless it is an unsigned TINYINT, a MySQL TINYINT datatype should be converted to the SQL Server SMALLINT datatype.

All MySQL integer types can have the optional UNSIGNED attribute. Unsigned integers take non-negative numbers only, but with a larger upper numeric range.

So unsigned integers can be always casted to larger SQL Server int types, for instance from unsigned SMALLINT to int:

UPDATE dbo.Table
            SET IntCol=CAST(UnsignedSmallInt AS INT)
        

Though, this will not work for the unsigned bigint. They have a larger Int64 type in SQL Server, but there are problems with handling of the BIGINT type.

Microsoft stated that:

"... At the time we implemented this functionality, there were issues supporting the Int64 data type across platforms. It was decided to return these values are strings instead." from the Microsoft Connect

So, that is the reason that forces you to give up up-casting unsigned bigints to SQL Server. You will likely have to convert the bigint data to string values.

Decimal data types.

MySQL MS SQL Server Mapping notes
DECIMAL(M,D) decimal(p,s) Default precision is 18.
The maximum precision is 38.
The default scale is 0.

Float or double data types are used for decimals with precision more than 38.

Floating Point data types

MySQL MS SQL Server
FLOAT(N) float(n)
FLOAT(M,D) float(24)
DOUBLE(M,D); REAL(M,D) float(53)
DOUBLE(M,D) smallmoney, money

Date and Time data types.

MySQL MS SQL Server Mapping notes
DDATETIME datetime2* It supports dates from 0001-01-01 through 9999-01-01
DATE date* It supports dates from 0001-01-01 to 9999-12-31
TIME time2* Time range is 00:00:00.0000000 through 23:59:59.9999999
TIMESTAMP smalldatetime smalldatetime value range is 1900-01-01 through 2079-06-06
YEAR Smallint -

*These date and time data types introduced in MS SQL 2008.

If you don’t use the NO_ZERO_DATE SQL mode, MySQL allows you to store ‘0000-00-00’ as “dummy dates”. Besides, invalid DATETIME, DATE, YEAR, or TIMESTAMP values are converted to “zero” equivalents of the corresponding type.

Otherwise, SQL Server can’t store “zero” dates and it is recommended that you represent zero date values as "1753 January 01". As well, string or number data types can be used for storing zero dates.

Date Validation

If ALLOW_INVALID_DATES flag is turned off, MySQL verifies that month value is in the range between 0 and 12 and day is in the range from 0 to 31.

New DateTime2 Data Type introduced in Microsoft SQL Server 2008 is able to validate date and time values as well.

String data types.

MySQL MS SQL Server Mapping notes
CHAR nchar(n), uniqueinentifier nchar allows 4000 characters
VARCHAR, TINYTEXT, TEXT(M), MEDIUMTEXT, LONGTEXT nvarchar(n|max) nvarchar allows 4000 characters but max indicates maximum storage size of 2^31-1 bytes.

In MySQL, ESCAPE sequence in a string literal begins with a backslash (‘\’).

For Example:

select 'This is a \'Quoted string\'';
            -- This is 'Quoted string'
        

In SQL Server, string literals have to be changed by duplicating the single quote character:

SELECT 'This is a ''Quoted string''';

Our software performs this type of transformation automatically.

Binary data types.

MySQL MS SQL Server Mapping notes
BINARY(M) binary(n) binary allows 8000 bytes
VARBINARY(M), TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB varbinary(n|max) nvarbinary allows 8000 bytes but max indicates maximum storage size of 2^31-1 bytes.
BLOB sql_variant -
LONGBLOB image -

Our programs can handle data types conversion automatically before migration keeping correct data at destination. They throw a warning message, if there is a potential problems with trimming of data.

Global Data types mapping may be configured. The whole database will be affected.

Global Mapping settings

Or Optionally you can map data types for certain fields manually.

Manual Custom Mapping settings

comments powered by Disqus