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.

When using UNSIGNED values, there is a potential problem with subtractions. When subtracting an UNSIGNED valued from another, the result is usually of an UNSIGNED type. But if the result is negative, this will cause an error. To solve this problem, we can enable the flag in sql_mode.

For more information see .

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: .

Aliases

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

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

Normally, REAL is also a synonym for FLOAT(24-53). However, the 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 for DECIMAL are recognized in both SQL Server and MariaDB: DEC, NUMERIC. MariaDB also allows one 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 with 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 type.

• Use a non-approximated type:

  • is very convenient, as it allows one 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.

There is a small incompatibility that users should be aware about. money and smallmoney are accurate to about 4 decimal digits. This means that, if you use enough decimal digits, operations on these types may produce different results than the results they would produce on MariaDB types.

Bits

The 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 and .

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

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 or BOOL, which is just a synonym for . TRUE and FALSE keywords also exist, but they are synonyms for 1 and 0. To understand what this implies, see .

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

Date and Time

You may also consider the following MariaDB types:

• 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 maximum allowed value is '2038-01-19 03:14:07'. Values are always stored in UTC. A TIMESTAMP column 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. Also, in MariaDB the range of TIMESTAMP values is

• 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 .

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 . They are not affected by NO_ZERO_DATE.

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 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 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 for more information.

Character Strings

One important difference between SQL Server and MariaDB is that in MariaDB character sets do not depend on types and collations. 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 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.

Notes:

1. If SQL Server uses a non-unicode collation, a subset of UTF-8 is used. So it is possible to use a smaller character set on MariaDB too.

2. has a maximum row length of 65,535 bytes. columns do not contribute to the row size, because they are stored separately (except for the first 12 bytes).

3. In SQL Server, UTF-16 is used if data contains Supplementary Characters, otherwise UCS-2 is used. If not sure, use utf16 in MariaDB.

SQL Server 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 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 . 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 . Therefore, for most cases storing the values as a string should be equivalent to using sql_variant.

Be aware that the maximum length of an sql_variant value is 8,000 bytes. In MariaDB, you may need to use TINYBLOB.

uniqueidentifier

While MariaDB does not support the uniqueidentifier type, the type can typically be used for the same purpose.

uniqueidentifier columns contain 16-bit GUIDs. MariaDB UUID columns store UUIDv1 values (128 bits).

The UUID type was implemented in . On older versions, you can generate unique values with the or functions, and store 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.

JSON

With SQL Server, typically JSON documents are stored in nvarchar columns in a text form.

MariaDB has a pseudo-type that maps to . However, from the JSON pseudo-type also checks that the value is valid a JSON document.

MariaDB supports different JSON functions than SQL Server. MariaDB currently has more functions, and SQL Server syntax will not work. See for more information.

MariaDB Specific Types

The following types are supported by MariaDB and don't have a direct equivalent in SQL Server. If you are migrating your database to MariaDB, you can consider using these types.

• - IPv6 addresses.

• - IPv4 addresses.

_{This page is licensed: CC BY-SA / Gnu FDL}