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
I am a technical and business professional based in Budapest, Hungary. I am working in the financial industry on the operations side, but also have test management experience in the educational software domain. My experience includes Microsoft SQL Server 2000-2012, IBM DB2 and MySQL database administration, troubleshooting, migration and development. My focus points are performance tuning and security. I am passionate about sharing my knowledge with the greater audience.
I hold MSc degree in Engineering and Management. I am married and I have a beautiful daughter.
LinkedIn account:
http://www.linkedin.com/in/nagytibor
Twitter account:
https://twitter.com/#!/tnagy_eeft
