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)
1	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)
2	CONVERT(DATETIME, string)	Convert string to datetime	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	PRIMARY KEY CLUSTERED	Primary key Constraint	PRIMARY KEY
4	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

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'

SQLines SQL Converter

SQLines Data

Migration to MariaDB

Migration to Oracle

Migration to SQL Server

Migration to MySQL

Migration to PostgreSQL

Migration to SingleStore

Migration to IBM DB2

Migration to Redshift

Migration to Snowflake

Migration to Apache Hive

Migration to Spark

Migration to Presto/Trino

Migration to Netezza

Migration to Greenplum

Migration to EsgynDB

Application Conversion

Database Reference