Syntax Differences between MariaDB and SQL Server

You are viewing an old version of this article. View the current version here.

Migrating from SQL Server? Contact the MariaDB migration experts!

  Contact Us

This 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 */;

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.