MySQL to Microsoft SQL Server Migration

SQLines tools can help you transfer data, convert database schema (DDL), views, stored procedures and functions, triggers, SQL queries and SQL scripts from MySQL to Microsoft SQL Server (MS SQL, MSSQL), Azure SQL and Azure Synapse.

Databases

  • MySQL 8.x and 5.x
  • Microsoft SQL Server 2022, 2019, 2017, 2016, 2014, 2012, 2008 and 2005

Migration Reference

SQL Language Elements

Converting language elements and constructs:

MySQL SQL Server
1 b'100' Binary string 0x04 Hex string only

Identifiers

Converting identifiers:

MySQL SQL Server
Quoted Identifiers ` (backtick) and " (double quotes) [ ] and " (double quotes)

Data Types

Converting character data types:

MySQL SQL Server
1 CHAR(n), CHARACTER(n) Fixed-length string, 1 ⇐ n ⇐ 255 CHAR(n), CHARACTER(n)
2 CHARACTER VARYING(n) Variable-length string, 1 ⇐ n ⇐ 65535 CHARACTER VARYING(n)
3 LONGTEXT Character large object, ⇐ 4G VARCHAR(max)
4 LONG, LONG VARCHAR Character large object, ⇐ 16M VARCHAR(max)
5 MEDIUMTEXT Character large object, ⇐ 16M VARCHAR(max)
6 NCHAR(n) Fixed-length UTF-8 string, 1 ⇐ n ⇐ 255 NCHAR(n)
7 NVARCHAR(n) Varying-length UTF-8 string, 1 ⇐ n ⇐ 65535 NVARCHAR(n)
8 TEXT Character large object, ⇐ 64K VARCHAR(max)
9 TINYTEXT Character data, ⇐ 255 bytes VARCHAR(255)
10 VARCHAR(n) Variable-length string, 1 ⇐ n ⇐ 65535 VARCHAR(n)

Converting numeric data types:

MySQL SQL Server
1 BIGINT 64-bit integer BIGINT
2 DECIMAL(p,s) Fixed-point number, p ⇐ 65, default 10 DECIMAL(p,s) p ⇐ 38, default 18
3 DEC(p,s) Synonym for DECIMAL DEC(p,s)
4 DOUBLE [PRECISION] Double-precision floating-point number FLOAT
5 FIXED(p,s) Synonym for DECIMAL DECIMAL(p,s)
6 FLOAT(p) Floating-point number FLOAT
7 FLOAT4(p) Floating-point number FLOAT
8 FLOAT8 Double-precision floating-point number BINARY_DOUBLE
9 INT, INTEGER 32-bit integer INT, INTEGER
10 INT1 8-bit integer SMALLINT
11 INT2 16-bit integer SMALLINT
12 INT3 24-bit integer INT
13 INT4 32-bit integer INT
14 INT8 64-bit integer BIGINT
15 MEDIUMINT 24-bit integer INT
16 MIDDLEINT 24-bit integer INT
17 NUMERIC(p,s) Synonym for DECIMAL NUMERIC(p,s)
18 REAL Double-precision floating-point number DOUBLE PRECISION
19 SERIAL 64-bit autoincrementing integer NUMERIC(20)
20 SMALLINT 16-bit integer SMALLINT
21 TINYINT 8-bit integer SMALLINT

Converting date and time data types:

MySQL SQL Server
1 DATE Date (year, month and day) DATE Since SQL Server 2008
2 DATETIME(p) Date and time data with fraction DATETIME2(p)
3 TIME(p) Time (Hour, minute, second and fraction) TIME(p)
4 TIMESTAMP(p) Auto-updated datetime DATETIME2(p)
5 YEAR[(2 | 4)] Year in 2-digit or 4-digit format NUMERIC(4)

Converting other data types:

MySQL SQL Server
1 BINARY(n) Fixed-length byte string, 1 ⇐ n ⇐ 255 BINARY(n)
2 BIT(n) Fixed-length bit string, 1 <= n <= 64 BINARY(n/8)
3 BLOB(n) Binary large object, ⇐ 64K VARBINARY(max)
4 BOOLEAN, BOOL 0 or 1 value; NULL is not allowed BIT
5 LONGBLOB Binary large object, ⇐ 4G VARBINARY(max)
6 LONG VARBINARY Binary large object, ⇐ 16M VARBINARY(max)
7 MEDIUMBLOB Binary large object, ⇐ 16M VARBINARY(max)
8 TINYBLOB Binary data, ⇐ 255 bytes VARBINARY(255)
9 VARBINARY(n) Variable-length byte string, 1 ⇐ n ⇐ 65535 VARBINARY(n)

Data Type Attributes:

MySQL SQL Server
Display width for integers INT(d) Not supported
UNSIGNED CHECK (col_name > 0)
AUTO_INCREMENT IDENTITY
COLLATE collate_name
CHARACTER SET charset_name
COMMENT 'string'
ON UPDATE clause for TIMESTAMP columns Not supported
BINARY(0) BINARY(1)

Built-in SQL Functions

Converting date and time functions:

MySQL SQL Server
1 CURRENT_TIMESTAMP CURRENT_TIMESTAMP() Get current date and time GETDATE()

CREATE TABLE Statement

Converting table definitions:

MySQL SQL Server
1 UNIQUE KEY | INDEX name (column, …) CONSTRAINT name UNIQUE (column, …)
2 KEY name (column, …) Inline non-unique index CREATE INDEX name ON table(column, …) Standalone statement

SELECT Statement

Converting SQL queries:

MySQL SQL Server
1 SELECT … FROM … LIMIT n Select n rows only SELECT TOP n … FROM …

CREATE PROCEDURE Statement

Converting stored procedures:

MySQL SQL Server
1 CREATE PROCEDURE name CREATE PROCEDURE name
2 DEFINER = user Removed
3 IN | OUT | INOUT param datatype(len) Parameter definition @param datatype(len) OUT | OUTPUT
4 No AS keyword before outer BEGIN END block AS keyword required
5 User-defined delimiter at the end GO

For more information, see Conversion of Procedural SQL Statements.

Procedural SQL Statements

Procedural SQL statements used in stored procedures, functions and triggers:

Flow-of-control statements:

MySQL SQL Server
1 IF … THEN … ELSEIF … ELSE … END IF IF statement IF … BEGIN … END ELSE IF BEGIN … END ELSE BEGIN … END