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](https://mariadb.com/docs/server/variables-and-modes/sql_mode#no_unsigned_subtraction) flag in sql\_mode.
For more information see [Numeric Data Type Overview](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/numeric-data-type-overview).
### Integer Numbers
| SQL Server Types | Size (bytes) | MariaDB Types | Size (bytes) | Notes |
| ---------------- | ------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------ | -------------------------------------------- |
| tinyint | 1 | [TINYINT](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/tinyint) | 1 | |
| smallint | 2 | [SMALLINT](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/smallint) | 2 | |
| | | [MEDIUMINT](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/mediumint) | 3 | Takes 3 bytes on disk, but 4 bytes in memory |
| int | 4 | [INT](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/int) / [INTEGER](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/integer) | 4 | |
| bigint | 8 | [BIGINT](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/bigint) | 8 | |
### Real Numbers (approximated)
| SQL Server Types | Precision | Size | MariaDB Types | Size |
| ---------------- | --------- | ---- | --------------------------------------------------------------------------------------------- | ---- |
| float(1-24) | 7 digits | 4 | [FLOAT(0-23)](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/float) | 4 |
| float(25-53) | 15 digist | 8 | [FLOAT(24-53)](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/float) | 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](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/floating-point-accuracy).
#### Aliases
In SQL Server `real` is an alias for `float(24)`.
In MariaDB [DOUBLE](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/double), and [DOUBLE PRECISION](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/double-precision) are aliases for `FLOAT(24-53)`.
Normally, `REAL` is also a synonym for `FLOAT(24-53)`. However, the [sql\_mode](https://mariadb.com/docs/server/server-management/variables-and-modes/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](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/decimal) | 0 - 38 | [See table](https://mariadb.com/docs/server/reference/data-types/data-type-storage-requirements#decimal) |
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](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/dec-numeric-fixed) 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](https://mariadb.com/docs/server/reference/data-types/string-data-types/enum) type.
* Use a non-approximated type:
* [DECIMAL](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/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](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/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](https://mariadb.com/docs/server/reference/sql-structure/sql-language-structure/binary-literals) and [Hexadecimal Literals](https://mariadb.com/docs/server/reference/sql-structure/sql-language-structure/hexadecimal-literals).
MariaDB and SQL Server have different sets of bitwise operators. See [Bit Functions and Operators](https://mariadb.com/docs/server/reference/sql-functions/secondary-functions/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](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/boolean) or `BOOL`, which is just a synonym for [TINYINT](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/tinyint). `TRUE` and `FALSE` keywords also exist, but they are synonyms for 1 and 0. To understand what this implies, see [Boolean Literals](https://mariadb.com/docs/server/reference/sql-structure/sql-language-structure/sql-language-structure-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](https://mariadb.com/docs/server/reference/data-types/date-and-time-data-types/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](https://mariadb.com/docs/server/reference/data-types/date-and-time-data-types/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](https://mariadb.com/docs/server/reference/data-types/date-and-time-data-types/datetime) | 001-01-01 - 9999-12-31 | 8 | 0 to 6 | MariaDB values are not approximated, see below. |
| smalldatetime | | | | [DATETIME](https://mariadb.com/docs/server/reference/data-types/date-and-time-data-types/datetime) | | | | |
| datetimeoffset | | | | [DATETIME](https://mariadb.com/docs/server/reference/data-types/date-and-time-data-types/datetime) | | | | |
| time | | | | [TIME](https://mariadb.com/docs/server/reference/data-types/date-and-time-data-types/time) | | | | |
You may also consider the following MariaDB types:
* [TIMESTAMP](https://mariadb.com/docs/server/reference/data-types/date-and-time-data-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 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
* [YEAR](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/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](https://mariadb.com/docs/server/variables-and-modes/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](https://mariadb.com/docs/server/variables-and-modes/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](https://mariadb.com/docs/server/reference/sql-structure/sql-language-structure/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](https://mariadb.com/docs/server/reference/data-types/data-type-storage-requirements#microseconds) for the details.
## String and Binary
### Binary Strings
| SQL Server Types | Size (bytes) | MariaDB Types | Notes |
| ---------------- | ------------ | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------ |
| binary | 1 to 8000 | [VARBINARY](https://mariadb.com/docs/server/reference/data-types/string-data-types/varbinary) or [BLOB](https://mariadb.com/docs/server/reference/data-types/string-data-types/blob-and-text-data-types) | See below for BLOB types |
| varbinary | 1 to 8000 | [VARBINARY](https://mariadb.com/docs/server/reference/data-types/string-data-types/varbinary) or [BLOB](https://mariadb.com/docs/server/reference/data-types/string-data-types/blob-and-text-data-types) | See below for BLOB types |
| image | 2^31-1 | [VARBINARY](https://mariadb.com/docs/server/reference/data-types/string-data-types/varbinary) or [BLOB](https://mariadb.com/docs/server/reference/data-types/string-data-types/blob-and-text-data-types) | 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](https://mariadb.com/docs/server/reference/data-types/string-data-types/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](https://mariadb.com/docs/server/variables-and-modes/server-system-variables#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](https://mariadb.com/docs/server/reference/data-types/string-data-types/char) | 0 to 255 | utf8mb4 (1, 4) |
| varchar | 1 to 8000 | [VARCHAR](https://mariadb.com/docs/server/reference/data-types/string-data-types/varchar) | 0 to 65,532 (2) | utf8mb4 (1) |
| text | 2^31-1 | [TEXT](https://mariadb.com/docs/server/reference/data-types/string-data-types/blob-and-text-data-types) | 2^31-1 | ucs2 |
| nchar | 2 to 8000 | [CHAR](https://mariadb.com/docs/server/reference/data-types/string-data-types/char) | 0 to 255 | utf16 or ucs2 (3, 4) |
| nvarchar | 2 to 8000 | [VARCHAR](https://mariadb.com/docs/server/reference/data-types/string-data-types/varchar) | 0 to 65,532 (2) (5) | utf16 or ucs2 (1) (3) |
| ntext | 2^30 - 1 | [TEXT](https://mariadb.com/docs/server/reference/data-types/string-data-types/blob-and-text-data-types) | 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](https://mariadb.com/docs/server/server-usage/storage-engines/innodb) has a maximum row length of 65,535 bytes. [TEXT](https://mariadb.com/docs/server/reference/data-types/string-data-types/blob-and-text-data-types) 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](https://mariadb.com/docs/server/variables-and-modes/sql_mode#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](https://mariadb.com/docs/server/reference/data-types/date-and-time-data-types/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](https://mariadb.com/docs/server/reference/sql-structure/temporal-tables/system-versioned-tables). 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](https://mariadb.com/docs/server/reference/sql-functions/string-functions/type-conversion). 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 [UUID](https://mariadb.com/docs/server/reference/data-types/string-data-types/uuid-data-type) 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 [MariaDB 10.7](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/10.7/what-is-mariadb-107). On older versions, you can generate unique values with the [UUID()](https://mariadb.com/docs/server/reference/sql-functions/secondary-functions/miscellaneous-functions/uuid) or [UUID\_SHORT()](https://mariadb.com/docs/server/reference/sql-functions/secondary-functions/miscellaneous-functions/uuid_short) 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 [JSON](https://mariadb.com/docs/server/reference/data-types/string-data-types/json) pseudo-type that maps to [LONGTEXT](https://mariadb.com/docs/server/reference/data-types/string-data-types/longtext). However, from [MariaDB 10.5](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/10.5/what-is-mariadb-105) 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](https://mariadb.com/docs/server/reference/sql-functions/special-functions/json-functions) 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.
* [INET6](https://mariadb.com/docs/server/reference/data-types/string-data-types/inet6) - IPv6 addresses.
* [INET4](https://mariadb.com/docs/server/reference/data-types/string-data-types/inet4) - IPv4 addresses.
*This page is licensed: CC BY-SA / Gnu FDL*
{% @marketo/form formId="4316" %}
---
# Agent Instructions: Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.
Perform an HTTP GET request on the current page URL with the `ask` query parameter:
```
GET 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.md?ask=
```
The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.