By: Tibor Nagy
Overview
Unfortunately MySQL and MS SQL Server use slightly different data types, so you will have to do some mapping to get the correct data after the migration. This chapter describes the most important data types in MySQL and their equivalents or recommended migration targets in SQL Server.
Explanation
The following tables show you the mapping of data types for data migration.
Integer
| MySQL | MS SQL Server | Conversion remarks |
|---|---|---|
| TINYINT | tinyint | tinyint is unsigned |
| SMALLINT | smallint | - |
| MEDIUMINT | int | int stores data on 4 bytes |
| INT | ||
| BIGINT | bigint | - |
Decimal
| MySQL | MS SQL Server | Conversion remarks |
|---|---|---|
| DECIMAL(M,D) | decimal(p,s) | The maximum precision is 38, while the default is 18. The default scale is 0. |
You can use the float or double data types for decimals with a precision greater than 38.
Floating Point
| MySQL | MS SQL Server | Conversion remarks |
|---|---|---|
| FLOAT(N) | float(n) | - |
| FLOAT(M,D) | float(24) | SQL Server does not allow the non-standard syntax |
| DOUBLE(M,D) | float(53) | SQL Server does not allow the non-standard syntax |
| REAL(M,D) |
Date and Time
| MySQL | MS SQL Server | Conversion remarks |
|---|---|---|
| DATETIME | datetime2 | Date values start at 0001-01-01 |
| DATE | date | Date values start at 0001-01-01 |
| TIME | time | timerange 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 | - |
Please note that MS SQL Server cannot store zeros as month or day. You can read
more about SQL Server 2008 Date and Time Data Types in
this tip.
String
| MySQL | MS SQL Server | Conversion remarks |
|---|---|---|
| CHAR | nchar(n) | nchar allows 4000 characters |
| VARCHAR | nvarchar(n|max) | nvarchar allows 4000 characters but max indicates maximum storage size of 2^31-1 bytes. |
| TINYTEXT | ||
| TEXT(M) | ||
| MEDIUMTEXT | ||
| LONGTEXT |
\' escape sequences in string constants should be replaced by double quote characters.
Binary
| MySQL | MS SQL Server | Conversion remarks |
|---|---|---|
| BINARY(M) | binary(n) | binary allows 8000 bytes |
| VARBINARY(M) | varbinary(n|max) | nvarbinary allows 8000 bytes but max indicates maximum storage size of 2^31-1 bytes. |
| TINYBLOB | ||
| BLOB | ||
| MEDIUMBLOB | ||
| LONGBLOB |
Additional Information
Data Types without MySQL Equivalent
- hierarchyid - http://msdn.microsoft.com/en-us/library/bb677290.aspx
- uniqueidentifier - http://msdn.microsoft.com/en-us/library/ms187942.aspx
- sql_variant - http://msdn.microsoft.com/en-us/library/ms173829.aspx
- table - http://msdn.microsoft.com/en-us/library/ms175010.aspx
Last Update: 5/27/2011
