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.

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 string functions:

SQL Server MySQL
1 CHAR(ascii_code) Convert ASCII code to character CHAR(ascii_code USING ASCII)
2 LEN(string) Length in characters excluding
trailing spaces
CHAR_LENGTH(RTRIM(string))

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)
8 MONTH(datetime) Extract month from datetime MONTH(datetime)
9 YEAR(datetime) Extract year from datetime YEAR(datetime)

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)

Data type conversion functions:

SQL Server MariaDB
1 CAST(exp AS datatype) Convert expression to another data type CAST(exp AS datatype)

NULL handling functions:

SQL Server MariaDB
1 ISNULL(exp, replacement) Replace NULL with the specified value IFNULL(exp, replacement)

Converting JSON functions:

SQL Server MariaDB
1 JSON_QUERY(json, path) Query JSON JSON_EXTRACT(json, path)
2 OPENJSON(json, path) Transform JSON to rows JSON_TABLE(json, path COLUMNS ...)

Converting other functions:

SQL Server MariaDB
1 NEWID() Generate UUID UUID()

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 CROSS APPLY Correlated inner join CROSS JOIN LATERAL
5 OUTER APPLY Correlated outer join LEFT OUTER JOIN LATERAL
6 STUFF((SELECT ... FOR XML PATH... Aggregate concatenation GROUP_CONCAT expression
7 SELECT ... FOR JSON PATH Return result as JSON SELECT ... FOR JSON PATH
8 LOOP | HASH | MERGE Join hints i.e. INNER LOOP JOIN Removed

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

CREATE FUNCTION Statement

Converting Transact-SQL user-defined functions:

SQL Server MariaDB
1 CREATE FUNCTION | ALTER FUNCTION name CREATE FUNCTION name
2 @param [AS] datatype = default p_param datatype
3 () Required for empty parameters ()
4 RETURNS datatype(len) RETURNS datatype(len)
5 RETURNS TABLE RETURNS JSON array
6 Optional AS before the function body Removed
7 Optional statement delimiter ; is added after each statement

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

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 more information, see Conversion of Transact-SQL Statements.

CREATE TRIGGER Statement

Converting Transact-SQL triggers:

SQL Server MariaDB
1 CREATE [OR ALTER] TRIGGER name CREATE [OR REPLACE] TRIGGER name
2 ON table Specified after the event type
3 FOR | AFTER | INSTEAD OF BEFORE | AFTER
4 INSERT, UPDATE, DELETE Multiple events allowed INSERT | UPDATE | DELETE Only one event per trigger
5 Specified before the event type ON table
6 Statement level trigger only FOR EACH ROW Only row level triggers
7 AS Removed
8 stmt; … BEGIN stmt; … END; //
9 GO Removed

Trigger system tables:

SQL Server MariaDB
1 inserted All inserted or updated rows NEW.col1, NEW.col2, ... Direct column reference

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

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

ALTER TRIGGER Statement

Modifying a trigger:

SQL Server MariaDB
1 ALTER TRIGGER name trigger_defintion CREATE OR REPLACE TRIGGER name trigger_defintion

For more information, see CREATE TRIGGER statement.

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'