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 NO_UNSIGNED_SUBTRACTION flag in sql_mode.

For more information see Numeric Data Type Overview.

Integer Numbers

SQL Server Types Size (bytes) MariaDB Types Size (bytes) Notes
tinyint 1 TINYINT 1
smallint 2 SMALLINT 2
MEDIUMINT 3 Takes 3 bytes on disk, but 4 bytes in memory
int 1 INT / INTEGER 4
bigint 8 BIGINT 8

Real Numbers (approximated)

SQL Server Types Precision Size MariaDB Types Size
float(1-24) 7 digits 4 FLOAT(0-23) 4
float(25-53) 15 digist 8 FLOAT(24-53) 8

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)

SQL Server Types Precision Size (bytes) MariaDB Types Precision Size (bytes)
decimal 0 - 38 Up to 17 DECIMAL 0 - 38 See table

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 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 ENUM type.
  • Use a non-approximated type:
    • DECIMAL 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 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:

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

SQL Server Types Range Precision Size (bytes) MariaDB Types Range Size (bytes) Precision Notes
date 0001-01-01 - 9999-12-31 3 / DATE 0001-01-01 - 9999-12-31 3 / They cover the same range
datetime 1753-01-01 - 9999-12-31 8 0 to 3, rounded DATETIME 001-01-01 - 9999-12-31 8 0 to 6 MariaDB values are not approximated, see below.
datetime2 001-01-01 - 9999-12-31 8 6 to 8 DATETIME 001-01-01 - 9999-12-31 8 0 to 6 MariaDB values are not approximated, see below.
smalldatetime DATETIME
datetimeoffset DATETIME
time 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.

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

SQL Server Types Size (bytes) MariaDB Types Notes
binary 1 to 8000 VARBINARY or BLOB See below for BLOB types
varbinary 1 to 8000 VARBINARY or BLOB See below for BLOB types
image 2^31-1 VARBINARY or BLOB See below for BLOB types

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

SQL Server Types Size (bytes) MariaDB Types Size (bytes) Character set
char 1 to 8000 CHAR 0 to 255 utf8mb4 (1, 4)
varchar 1 to 8000 VARCHAR 0 to 65,532 (2) utf8mb4 (1)
text 2^31-1 TEXT 2^31-1 ucs2
nchar 2 to 8000 CHAR 0 to 255 utf16 or ucs2 (3, 4)
nvarchar 2 to 8000 VARCHAR 0 to 65,532 (2) (5) utf16 or ucs2 (1) (3)
tnext 2^30 - 1 TEXT 2^31-1 ucs2

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) InnoDB has a maximum row length of 65,535 bytes. TEXT 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.

4) In SQL Server, the value of ANSI_PADDING determines if char values should be padded with spaces to their maximum length. In MariaDB, this depends on the PAD_CHAR_TO_FULL_LENGTH sql_mode flag.

5) See JSON, below.

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

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

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.

JSON

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

MariaDB has a JSON pseudo-type that maps to LONGTEXT. However, from MariaDB 10.5 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 JSON functions for more information.

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.

© 2021 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/sql-server-and-mariadb-types-comparison/