Migrating MySQL Data Types to SQL Server 2008

Migrating MySQL Data Types

This section explains mappings and differences between MySQL and SQL Server 2008 data types, specific data type handling, and provides solutions for problems related to data types.

Type Mapping

Following are recommended type mappings for converting column data types while migrating columns, subroutines etc.

 

MySql Type

Sql Server 2008 Mappings

Remarks

Alternatives

TINYINT (M)

tinyint

M is the number of decimal places in the output for this value.

tinyint, smallint, int, bigint, numeric(p,s), decimal(p,s), float(p), double precision, real,

TINYINT (1)

 

 

smallmoney,money

SMALLINT (M)

smallint

M is the number of decimal places in the output for this value.

tinyint, smallint, int, bigint, numeric(p,s), decimal(p,s), float(p), double precision, real, smallmoney, money

MEDIUMINT (M)

int

M is the number of decimal places in the output for this value.

tinyint, smallint, int, bigint, numeric(p,s), decimal(p,s), float(p), double precision, real, smallmoney, money

INT (M)

INTEGER (M)

int

M is the number of decimal places in the output for this value.

tinyint, smallint, int, bigint, numeric(p,s), decimal(p,s), float(p), double precision, real, smallmoney, money

BIGINT (M)

bigint

M is the number of decimal places in the output for this value.

tinyint, smallint, int, bigint, numeric(p,s), decimal(p,s), float(p), double precision, real, smallmoney, money

FLOAT (P)

float(P)

N/A

numeric(p,s), decimal(p,s), float(p), double precision, real, smallmoney, money

FLOAT [(P, S)]

float(24)

(P,S)” means that values are displayed with up to P digits total, of which S digits may be after the decimal point. MySQL performs rounding when storing values.

numeric(p,s), decimal(p,s), float(p), double precision, real, smallmoney, money

DATETIME [(D)]

datetime2

MySQL store dates from 0000-00-00 to 9999-12-31. MySQL can store zero-value of year, month, and year.

smalldatetime,datetime

DATE [(D)]

date

 

smalldatetime, datetime, datetime2, date

TIME

time

Range is '-838:59:59' to '838:59:59'.

smalldatetime,datetime,

time,varchar,nvarchar

TIMESTAMP

smalldatetime

 

datetime, datetime2, rowversion, timestamp, varbinary(8), binary(8)

[NATIONAL] CHAR (N)

nchar(N)

Range of N is 0 to 255 characters.

char, varchar, nchar, nvarchar

TINYTEXT

nvarchar(255)

None

char, varchar, nchar, nvarchar

MEDIUMTEXT

nvarchar(max)

None

char, varchar, nchar, nvarchar, varchar(max), nvarchar(max)

BINARY (N)

binary(N)

None

binary, varbinary, char, varchar, nchar, nvarchar

TINYBLOB

varbinary(255)

None

binary, varbinary, varbinary(max)

BLOB (N)

varbinary (N | max)

A BLOB column with a maximum length of 65,535 bytes.

binary, varbinary, varbinary(max)

I hope this section will help you out while migrating MySql to SqlServer. Thanks for reading!

Ebook Download
View all
Learn
View all