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.

When using

For more information see Numeric Data Type Overview.

Integer Numbers

SQL Server TypesSize (bytes)MariaDB TypesSize (bytes)Notes
tinyint1TINYINT1
smallint2SMALLINT2
MEDIUMINT3Takes 4 bytes in memory
int1INT / INTEGER4
bigint8BIGINT8

Real Numbers (approximated)

SQL Server TypesPrecisionSizeMariaDB TypesPrecisionSizeNotes
float(1-24)7 digits4FLOAT(0-23)4
float(25-53)15 digist8FLOAT(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 TypesPrecisionSize (bytes)MariaDB TypesPrecisionSize (bytes)
decimal0 - 38Up to 17DECIMAL0 - 38See 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 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.

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 TypesRangePrecisionSize (bytes)MariaDB TypesRangeSize (bytes)PrecisionNotes
date0001-01-01 - 9999-12-313/DATE0001-01-01 - 9999-12-313/They cover the same range
datetime1753-01-01 - 9999-12-3180 to 3, roundedDATETIME001-01-01 - 9999-12-3180 to 6MariaDB values are not approximated, see below.
datetime2001-01-01 - 9999-12-3186 to 8DATETIME001-01-01 - 9999-12-3180 to 6MariaDB values are not approximated, see below.
smalldatetimeDATETIME
datetimeoffsetDATETIME
timeTIME

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 TypesSize (bytes)MariaDB TypesNotes
binary1 to 8000VARBINARY or BLOBSee below for BLOB types
varbinary1 to 8000VARBINARY or BLOBSee below for BLOB types
image2^31-1VARBINARY or BLOBSee 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 TypesSize (bytes)MariaDB TypesSize (bytes)Character set
char1 to 8000CHAR0 to 255utf8mb4 (1, 4)
varchar1 to 8000VARCHAR0 to 65,532 (2)utf8mb4 (1)
text2^31-1TEXT2^31-1ucs2
nchar2 to 8000CHAR0 to 255utf16 or ucs2 (3, 4)
nvarchar2 to 8000VARCHAR0 to 65,532 (2)utf16 or ucs2 (1) (3)
tnext2^30 - 1TEXT2^31-1ucs2

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 from the PAD_CHAR_TO_FULL_LENGTH sql_mode flag.

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.

Comments

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