Some MariaDB features are not available in SQL Server.
At first glance, it is not important to know about those features to migrate from SQL Server to MariaDB. However, this is not the case. Using MariaDB features that are not in SQL Server allows one to obtain more advantages from the migration, getting the most from MariaDB.
This page has a list of MariaDB features that are not supported in SQL Server. The list is not exhaustive.
## Plugin Architecture
* [Storage engines](https://mariadb.com/docs/server/server-usage/storage-engines).
* [Authentication plugins](https://mariadb.com/docs/server/reference/plugins/authentication-plugins).
* [Encryption plugins](https://mariadb.com/docs/server/security/encryption/data-at-rest-encryption/key-management-and-encryption-plugins).
* [ColumnStore](https://app.gitbook.com/o/diTpXxF5WsbHqTReoBsS/s/rBEU9juWLfTDcdwF3Q14/) is a columnar storage engine designed to scale horizontally. It runs on a specific edition of MariaDB, so currently it cannot be used in combination with other engines.
## SQL
* The [sql\_mode](https://mariadb.com/docs/server/server-management/variables-and-modes/sql_mode) variable determines in which cases an SQL statement should fail with an error, and in which cases it should succeed with a warning even if it is not entirely correct. For example, when a statement tries to insert a string in a column which is not big enough to contain it, it could fail, or it could insert a truncated string and emit a warning. It is a tradeoff between reliability and flexibility.
* [SQL\_MODE=MSSQL](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/about/compatibility-and-differences/sql_modemssql) allows one to use a small subset of SQL Server proprietary syntax.
* The [CREATE ... IF EXISTS, CREATE OR REPLACE, DROP ... IF NOT EXISTS](https://mariadb.com/docs/server/server-management/install-and-upgrade-mariadb/migrating-to-mariadb/syntax-differences-between-mariadb-and-sql-server#if-exists-if-not-exists-or-replace) options are supported for most [DDL statements](https://mariadb.com/docs/server/reference/sql-statements/data-definition).
* [SHOW](https://mariadb.com/docs/server/server-management/install-and-upgrade-mariadb/migrating-to-mariadb/syntax-differences-between-mariadb-and-sql-server#show-statements) statements.
* [SHOW CREATE](https://mariadb.com/docs/server/server-management/install-and-upgrade-mariadb/migrating-to-mariadb/syntax-differences-between-mariadb-and-sql-server#show-create-statements) statements.
* [SHOW PROCESSLIST](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-processlist) and [PERFORMANCE\_SCHEMA THREAD table](https://mariadb.com/docs/server/reference/system-tables/performance-schema/performance-schema-tables/performance-schema-threads-table) provide much richer information, compared to SQL Server `sp_who()` and `sp_who2()` procedures.
* [CHECKSUM TABLE](https://mariadb.com/docs/server/reference/sql-statements/table-statements/checksum-table) statement.
* [PL/SQL support](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/about/compatibility-and-differences/sql_modeoracle) (only for stored procedures and stored functions).
* Row constructors.
* `BEFORE` [triggers](https://mariadb.com/docs/server/server-usage/triggers-events/triggers).
* [HANDLER](https://mariadb.com/docs/server/reference/sql-structure/nosql/handler/handler-commands) statements, to scroll table rows ordered by an index or in their physical order.
* [DO](https://mariadb.com/docs/server/reference/sql-statements/stored-routine-statements/do) statement, to call functions without returning a result set.
* [BENCHMARK()](https://mariadb.com/docs/server/reference/sql-functions/secondary-functions/information-functions/benchmark) function, to measure the speed of an SQL expression.
See also [Syntax Differences between MariaDB and SQL Server](https://mariadb.com/docs/server/server-management/install-and-upgrade-mariadb/migrating-to-mariadb/migrating-to-mariadb-from-sql-server/syntax-differences-between-mariadb-and-sql-server).
## Types
* [Character sets and collations](https://mariadb.com/docs/server/reference/data-types/string-data-types/character-sets) don't depend on column type. They can be set globally, or at database, table or column level.
* Columns may use non-constant expressions as the [DEFAULT](https://mariadb.com/docs/server/server-usage/tables/create-table#default-column-option) value. [TIMESTAMP](https://mariadb.com/docs/server/reference/data-types/date-and-time-data-types/timestamp) columns may have a `DEFAULT` value.
* [UNSIGNED](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/numeric-data-type-overview#signed-unsigned-and-zerofill) numeric types.
* [Dynamic columns](https://mariadb.com/docs/server/reference/sql-structure/nosql/dynamic-columns) (note that JSON is usually preferred to this feature).
See also [SQL Server and MariaDB Types Comparison](https://mariadb.com/docs/server/server-management/install-and-upgrade-mariadb/migrating-to-mariadb/migrating-to-mariadb-from-sql-server/sql-server-and-mariadb-types-comparison).
### JSON
For compatibility with some other database systems, MariaDB supports the [JSON](https://mariadb.com/docs/server/reference/data-types/string-data-types/json) pseudo-type. However, it is just an alias for:
`LONGTEXT CHECK (JSON_VALID(column_name))`
[JSON\_VALID()](https://mariadb.com/docs/server/reference/sql-functions/special-functions/json-functions/json_valid) is the MariaDB equivalent of SQL Server's `ISJSON()`.
## Features
* [Flashback](https://mariadb.com/docs/server/server-management/server-monitoring-logs/binary-log/flashback) functionality allows one to "undo" the changes that happened after a certain point in time.
* [Partitioned tables](https://mariadb.com/docs/server/server-usage/partitioning-tables) support the following features:
* Tables can be partitioned based on [multiple columns](https://mariadb.com/docs/server/server-usage/partitioning-tables/partitioning-types/range-columns-and-list-columns-partitioning-types).
* Several [partitioning types](https://mariadb.com/docs/server/server-usage/partitioning-tables/partitioning-overview#partitioning-types) are available.
* Subpartitions.
* [Progress reporting](https://mariadb.com/docs/server/server-management/install-and-upgrade-mariadb/migrating-to-mariadb/migrating-to-mariadb-from-sql-server/broken-reference) for some typically expensive statements.
*This page is licensed: CC BY-SA / Gnu FDL*
{% @marketo/form formId="4316" %}