Syntax Differences between MariaDB and SQL Server

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

Some features are meant to improve syntax and semantics compatibility between MariaDB versions, between MariaDB and MySQL, and between MariaDB and other DBMSs. This section focuses on the compatibility between MariaDB and SQL Server.

sql_mode and old_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). sql_mode must be assigned a comma-separated list of flags.

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');

old_mode is very similar to sql_mode, but its purpose is to provide compatibility with older MariaDB versions. Its flags shouldn't affect the compatibility with SQL Server (though it is theoretically possible that some of them do, as a side effect).

Executable Comments

MariaDB supports executable comments. These are designed to write generic queries that are only executed by MariaDB, and optionally only certain versions.

The following examples show how to insert SQL code that will be ignored by SQL Server but executed by MariaDB, or some of its 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 */;

Data Definition Language

MariaDB supports useful syntax that SQL Server users may find useful.

IF EXISTS, IF NOT EXISTS, OR REPLACE

Most DDL statements, including ALTER TABLE, support the following syntax:

• DROP IF EXISTS: A warning (not an error) is produced if the object does not exist.
• OR REPLACE: If the object exists, it is dropped and recreated; otherwise it is created. This operation is atomic, so in no point in time the object does not exist.
• CREATE IF NOT EXISTS: If the object already exists, a warning (not an error) is produced. The object will not be replaced.

These statements are functionally similar (but less verbose) than SQL Server snippets similar to the following:

IF NOT EXISTS (SELECT name FROM sysobjects WHERE name = 'my_table' AND xtype = 'U')
    CREATE TABLE my_table (
        ...
    )
go

SHOW CREATE

In general, for each CREATE statement MariaDB also supports a SHOW CREATE statement. For example there is a SHOW CREATE TABLE that returns the CREATE TABLE statement that can be used to recreate a table.

See Also

• SQL Server and MariaDB Types Comparison
• MariaDB Transactions and Isolation Levels for SQL Server Users
• SQL_MODE=MSSQL