SQL Server and MariaDB Types Comparison

This page helps to map each SQL Server type to the matching MariaDB type.

Numbers

In MariaDB numeric types can be declared as SIGNED or UNSIGNED. By default numeric columns are SIGNED, so not specifying either will not break compatibility with SQL Server.

For more information see Numeric Data Type Overview.

Integer Numbers

Real Numbers (approximated)

MariaDB supports an alternative syntax: FLOAT(M, D). M is the total number of digits, and D is the number of digits after the decimal point.

See also: Floating-point Accuracy.

Aliases

In SQL Server real is an alias for float(24).

In MariaDB DOUBLE, and DOUBLE PRECISION are aliases for FLOAT(24-53).

Normally, REAL is also a synonym for FLOAT(24-53). However, the sql_mode variable can be set with the REAL_AS_FLOAT flag to make REAL a synonym for FLOAT(0-23).

Real Numbers (exact)

MariaDB supports this syntax: DECIMAL(M, D). M and D are both optional. M is the total number of digits (10 by default), and D is the number of digits after the decimal point (0 by default). In SQL Server, defaults are 18 and 0, respectively. The reason for this difference is that SQL standard imposes a default of 0 for D, but it leaves the implementation free to choose any default for M.

SQL Server DECIMAL is equivalent to MariaDB DECIMAL(18).

Aliases

The following aliases for DECIMAL are recognized in both SQL Server and MariaDB: DEC, NUMERIC. MariaDB also allows to use FIXED.

Money

SQL Server money and smallmoney types represent real numbers guaranteeing a very low level of approximation (five decimal digits are accurate), optionally associated to one of the supported currencies.

MariaDB doesn't have monetary types. To represent amounts of money:

• Store the currency in a separate column, if necessary. It's possible to use a foreign key to a currencies table, or the ENUM type.
• Use a non-approximated type:
  • DECIMAL is very convenient, as it allows to store the number as-is. But calculations are potentially slower.
  • An integer type is faster for calculations. It is possible to store, for example, the amount of money multiplied by 100.

Bits

The BIT type is supported in MariaDB. Its maximum size is BIT(64). The BIT type has a fixed length. If we insert a value which requires less bits than the ones that are allocated, zero-bits are padded on the left.

In MariaDB, binary values can be written in one of the following ways:

• b'value'
• 0value where value is a sequence of 0 and 1 digits. Hexadecimal syntax can also be used. For more details, see Binary Literals and Hexadecimal Literals.

MariaDB and SQL Server have different sets of bitwise operators. See Bit Functions and Operators.

BOOLEAN pseudo-type

In SQL Server, it is common to use bit to represent boolean values. In MariaDB it is possible to do the same, but this is not a common practice.

A column can also be defined as BOOLEAN or BOOL, which is just a synonym for TINYINT. TRUE and FALSE keywords also exist, but they are synonyms for 1 and 0. To understand what this implies, see Boolean Literals.

In MariaDB 'True' and 'False' are always strings.

Date and Time

You may also consider the following MariaDB types:

• TIMESTAMP has little to do with SQL Server's timestamp. In MariaDB it is the number of seconds elapsed since the beginning of 1970-01-01, with a decimal precision up to 6 digits (0 by default). The value can optionally be automatically set to the current timestamp on insert, on update or both. It is not meant to be a unique row identifier.
• YEAR is a 1-byte type representing years between 1901 and 2155, as well as 0000.

Zero values

MariaDB allows a special value where all the parts of a date are zeroes: '0000-00-00'. This can be disallowed by setting sql_mode=NO_ZERO_DATE.

It is also possible to use values where only some date parts are zeroes, for example '1994-01-00' or '1994-00-00'. These values can be disallowed by setting sql_mode=NO_ZERO_IN_DATE. They are not affected by NO_ZERO_DATE.

See sql_mode for more information about this server variable.

Syntax

Several different date formats are understood. Typically used formats are 'YYYY-MM-DD' and YYYYMMDD. Several separators are accepted.

The syntax defined in standard SQL and ODBC are understood - for example, DATE '1994-01-01' and {d '1994-01-01'} . Using these eliminates possible ambiguities in contexts where a temporal value could be interpreted as a string or as an integer.

See Date and Time Literals for the details.

Precision

For temporal types that include a day time, MariaDB allows a precision from 0 to 6 (microseconds), 0 being the default. The subsecond part is never approximated. It adds up to 3 bytes. See Data Type Storage Requirements for the details.

String and Binary

Binary Strings

The VARBINARY type is similar to VARCHAR, but stores binary byte strings, just like SQL Server binary does.

For large binary strings, MariaDB has four BLOB types, with different sizes. See BLOB and TEXT Data Types for more information.

Character Strings

One important difference between SQL Server and MariaDB is that in MariaDB character sets are independent from types. Character sets can be set at database, table or column level. If this is not done the default character sets applies, which is specified by the character_set_server system variable.

To create a MariaDB table that is identical to a SQL Server table, it may be necessary to specify a character set for each string column. However, in many cases using UTF-8 will work.

Special Types

rowversion

MariaDB does not have the rowversion type.

If the only purpose is to check if a row has been modified since its last read, a TIMESTAMP column can be used instead. Its default value should be ON UPDATE CURRENT_TIMESTAMP. In this way, the timestamp will be updated whenever the column is modified.

A way to preserve much more information is to use a temporal table. Past versions of the row will be preserved.

sql_variant

MariaDB does not support the sql_variant type.

MariaDB is quite flexible about implicit and explicit type conversions. Therefore, for most cases storing the values as a string should be equivalent to using sql_variant.

uniqueidentifier

MariaDB does not support the uniqueidentifier type.

uniqueidentifier columns contain 16-bit GUIDs. MariaDB can generate unique values with the UUID() or UUID_SHORT() functions, and stored them in BIT(128) or BIT(64) columns, respectively.

xml

MariaDB does not support the xml type.

XML data can be stored in string columns. MariaDB supports several XML functions.