Microsoft SQL Server (MS SQL) to MariaDB Migration

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

  • SQLines Data - Data transfer, schema migration and validation tool
  • SQLines SQL Converter - SQL scripts assessment and conversion tool

Databases:

  • Microsoft SQL Server 2022, 2019, 2017, 2016, 2014, 2012, 2008, 2005 and 2000, Azure SQL Database, Azure Synapse
  • MariaDB 11.x and 10.x

Migration Reference

SQL Language Elements

Converting SQL language elements:

SQL Server MariaDB
1 func (param, ...) Blank after function name func(param, ...) Blank is not always allowed
2 string + string2 + … String concatenation CONCAT(string, string2, …)
3 @@IDENTITY Returns the last-inserted identity value LAST_INSERT_ID()

Datetime arithmetics:

SQL Server MariaDB
1 GETDATE() + n Add n days to a datetime ADDDATE(NOW(), n)
DATE_ADD(NOW(), INTERVAL n DAY)
TIMESTAMPADD(DAY, n, NOW())
2 GETDATE() + 0.1 Add 0.1 of 24 hours i.e. 144 minutes DATE_ADD(NOW(), INTERVAL 144 MINUTE)

Data Types

Character data types:

SQL Server MariaDB
1 NTEXT Variable-length Unicode UCS-2 data, <= 2G LONGTEXT

Numeric data types:

SQL Server MariaDB
1 BIGINT 64-bit integer BIGINT
2 DECIMAL(p,s) Fixed-point number DECIMAL(p,s)
3 DOUBLE PRECISION Double-precision floating-point number DOUBLE PRECISION
4 FLOAT(p) Floating-point number DOUBLE
5 INT, INTEGER 32-bit integer INT, INTEGER
6 MONEY 64-bit currency amount DECIMAL(15,4)
7 NUMERIC(p,s) Fixed-point number NUMERIC(p,s)
8 REAL Single-precision floating-point number REAL
9 SMALLINT 16-bit integer SMALLINT
10 SMALLMONEY 32-bit currency amount DECIMAL(6,4)
11 TINYINT 8-bit unsigned integer, 0 to 255 TINYINT UNSIGNED

Datetime data types:

SQL Server MariaDB
1 DATE Date (year, month and day) DATE
2 DATETIME Date and time with fraction (milliseconds) DATETIME(3)
3 DATETIME2(p) Date and time with fraction, 0 <= p <= 7 DATETIME(p) p <= 6
4 DATETIMEOFFSET(p) Date and time with fraction and time zone , 0 <= p <= 7 DATETIME(p) p <= 6
5 SMALLDATETIME Datetime (year, month, day, hour, minutes and 00 seconds ) DATETIME
6 TIME(p) Time (Hour, minute, second and fraction), 0 <= p <= 7 TIME(p) p <= 6

Other data types:

SQL Server MariaDB
1 SYSNAME System-defined type for identifiers NVARCHAR(128)

Built-in SQL Functions

Converting datetime functions:

SQL Server MariaDB
1 CAST(string AS DATETIME) Convert string to datetime STR_TO_DATE(string, format)
CAST(0x00.. AS DATETIME) Convert hex value to datetime CAST and interval expression
2 CONVERT(DATETIME, string, style) Convert string to datetime STR_TO_DATE(string, format)
CONVERT(DATETIME, string) CONVERT(string, DATETIME)
3 CONVERT(VARCHAR, datetime, style) Convert datetime to string DATE_FORMAT(datetime, format)
4 DATEADD(unit, value, exp) Add datetime interval TIMESTAMPADD(unit, value, exp)
5 DATEDIFF(units, start, end) Get datetime difference in specified units TIMESTAMPDIFF(units, start, end)
6 FORMAT(datetime, format) Convert to string with format DATE_FORMAT(datetime, format)
7 GETDATE() Get the current date and time NOW(3)

Converting numeric functions:

SQL Server MariaDB
1 CONVERT(NUMERIC, exp) Convert exp to number or truncate CONVERT(exp, DECIMAL) Param order,
NUMERIC not supported
CONVERT(DECIMAL, exp)

ALTER TABLE Statement

Modifying a table:

SQL Server MariaDB
1 ALTER TABLE table ADD DEFAULT exp FOR column Add column default Moved to CREATE TABLE

CREATE TABLE Statement

Converting CREATE TABLE statement keywords and clauses:

SQL Server MariaDB
1 IDENTITY(start, increment) Identity column AUTO_INCREMENT Increment is always 1
2 IDENTITY can be defined on DECIMAL/NUMERIC columns Integer columns must be used
3 CONSTRAINT name DEFAULT value Named DEFAULT DEFAULT value
4 PRIMARY KEY CLUSTERED Primary key Constraint PRIMARY KEY
5 col AS exp Computed column type col AS (exp) Type must be specified

Storage clause:

SQL Server MariaDB
1 ON PRIMARY | filegroup File group to store the table Removed
2 TEXTIMAGE_ON PRIMARY | filegroup File group to store large objects of the table Removed

SELECT Statement

Converting SQL queries from SQL Server to MariaDB:

SQL Server MariaDB
1 SELECT TOP n … FROM … Select n rows only SELECT … FROM … LIMIT n
2 SELECT @v = c, @v2 = c2 FROM … SELECT INTO statement SELECT c, c2 INTO v, v2 FROM …
3 SELECT ... INTO #tmp Create a temporary table
using SELECT
CREATE TEMPORARY TABLE tmp AS SELECT ...
4 STUFF((SELECT ... FOR XML PATH... Aggregate concatenation GROUP_CONCAT expression
5 LOOP | HASH | MERGE Join hints i.e. INNER LOOP JOIN Removed

CREATE PROCEDURE Statement

Converting Transact-SQL stored procedures:

SQL Server MariaDB
1 CREATE PROCEDURE name CREATE PROCEDURE name
2 @param datatype [= default] [OUTPUT] IN | OUT | INOUT p_param datatype no defaults
3 Optional () for procedure parameters () required
4 AS before the procedure body Removed
5 BEGIN END is optional for the procedure body BEGIN END required
6 Optional statement delimiter ; is added after each statement
7 GO Specified by DELIMITER command

For further information, see Conversion of Transact-SQL Statements.

Transact-SQL Statements

Converting Transact-SQL statements from SQL Server to MariaDB:

Variable declaration and assignment:

SQL Server MariaDB
1 DECLARE @var [AS] type [= default_value] DECLARE var type [DEFAULT default_value]
2 DECLARE @v1 type1, @v2 type2, … DECLARE v1 type1; DECLARE v2 type2, DECLARE …
3 DECLARE @tab TABLE CREATE TEMPORARY TABLE vtab
4 SET @var = expression SET var = expression
5 SET @var = (SELECT expression FROM …) SET var = (SELECT expression FROM …)

Flow-of-control statements:

SQL Server MariaDB
1 BREAK Exit a loop LEAVE label
2 IF condition BEGIN … END IF statement IF condition THEN … END IF;
3 IF … ELSE IF … IF ELSE IF statement IF … ELSEIF …
4 WHILE condition BEGIN stmts END Conditional loop WHILE condition DO stmts END WHILE;

Exception block:

SQL Server MariaDB
1 BEGIN TRY … END TRY
BEGIN CATCH … END CATCH
Exception block DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN … END

Transaction statements:

SQL Server MariaDB
1 BEGIN TRANSACTION | TRAN Begin a transaction START TRANSACTION
2 COMMIT TRANSACTION | TRAN Commit transaction COMMIT
3 ROLLBACK TRANSACTION | TRAN Rollback transaction ROLLBACK

Other Transact-SQL statements:

SQL Server MariaDB
1 PRINT 'text' Send message to the client Commented inside a procedural block
SELECT 'text' AS '' Expression in SQL script
\! echo 'text'; String literal in SQL script

System Procedures

Converting system procedure calls from SQL Server to MariaDB:

SQL Server MariaDB
1 sp_addextendedproperty 'MS_Description',
'Table comment', … 'table', 'table_name'
Comment on table ALTER TABLE table_name COMMENT 'Table comment'