Syntax Differences between MariaDB and SQL Server
Migrating from SQL Server? Contact the MariaDB migration experts!
Contact UsThis article is meant to show a non-exhaustive list of syntax differences between MariaDB and SQL Server, and it's written for SQL Server users that are unfamiliar with MariaDB.
Compatibility Features
sql_mode
The SQL semantics and syntax, in MariaDB, are affected by the sql_mode
variable. Its value is a comma-separated list of flags, and each of them if specified affects a different aspects of the SQL syntax and semantics.
A particularly important flag for users familiar with SQL Server is MSSQL
.
sql_mode can be changed locally, in which case it only affects the current session; or globally, in which case it will affect all new connections (but not the connections already established).
A usage example:
# check the current global and local sql_mode values SELECT @@global.sql_mode; SELECT @@session.sql_mode; # empty sql_mode for all usaers SET GLOBAL sql_mode = ''; # add MSSQL flag to the sql_mode for the current session SET SESSION sql_mode = CONCAT(sql_mode, ',MSSQL');
Executable Comments
MariaDB supports executable comments. These are designed to write generic queries that are only executed by MariaDB, and optionally only certain versions.
- Executed by MariaDB and MySQL:
SELECT * FROM tab /*! FORCE INDEX (idx_a) */ WHERE a = 1 OR b = 2;
- Executed by MariaDB only:
SELECT * /*!M , @in_transaction */ FROM tab;
- Executed by MariaDB starting from version 10.0.5:
DELETE FROM user WHERE id = 100 /*!M100005 RETURNING email */;