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:
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) |
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:
Other data types:
SQL Server | MariaDB | |||
1 | SYSNAME | System-defined type for identifiers | NVARCHAR(128) |
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() |
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 |
Converting CREATE TABLE statement keywords and clauses:
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 |
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.
Converting Transact-SQL stored procedures:
For more information, see Conversion of Transact-SQL Statements.
Converting Transact-SQL triggers:
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.
Modifying a table:
SQL Server | MariaDB | ||
1 | ALTER TABLE table ADD DEFAULT exp FOR column | Add column default | Moved to CREATE TABLE |
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.
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 |
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' |