Detailed information about Microsoft SQL Server Data Types and its mapping to another databases and program languages analog.
- Data Type Precedence (Transact-SQL)
- Data Type Synonyms (Transact-SQL)
- Precision, Scale, and Length (Transact-SQL)
- SQL Server, SSIS and Biml Data Types
- SQL Server Data Types Length
- SQL Server to MySQL, Oracle, PostgreSQL and SQLite Data Type Mapping
- SQL Server Data Types Mapping to Program Languages
- Source links
When an operator combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence. If the conversion is not a supported implicit conversion, an error is returned. When both operand expressions have the same data type, the result of the operation has that data type.
SQL Server uses the following precedence order for data types:
- sql_variant (highest)
- xml
- datetimeoffset
- datetime2
- datetime
- smalldatetime
- date
- time
- float
- real
- decimal
- money
- smallmoney
- bigint
- int
- smallint
- tinyint
- bit
- ntext
- text
- image
- timestamp
- uniqueidentifier
- nvarchar (including nvarchar(max))
- nchar
- varchar (including varchar(max))
- char
- varbinary (including varbinary(max))
- binary (lowest)
Data type synonyms are included in SQL Server for ISO compatibility. The following table lists the synonyms and the SQL Server system data types that they map to.
| Synonym | System data type |
|---|---|
| Binary varying | varbinary |
| char varying | varchar |
| character | char |
| character | char(1) |
| character(n) | char(n) |
| character varying(n) | varchar(n) |
| Dec | decimal |
| Double precision | float |
| float[(n)] for n = 1-7 | real |
| float[(n)] for n = 8-15 | float |
| integer | int |
| national character(n) | nchar(n) |
| national char(n) | nchar(n) |
| national character varying(n) | nvarchar(n) |
| national char varying(n) | nvarchar(n) |
| national text | ntext |
| timestamp | rowversion |
Data type synonyms can be used instead of the corresponding base data type name in data definition language (DDL) statements, such as CREATE TABLE, CREATE PROCEDURE, or DECLARE @variable. However, after the object is created, the synonyms have no visibility. When the object is created, the object is assigned the base data type that is associated with the synonym. There is no record that the synonym was specified in the statement that created the object.
Precision is the number of digits in a number. Scale is the number of digits to the right of the decimal point in a number. For example, the number 123.45 has a precision of 5 and a scale of 2.
In SQL Server, the default maximum precision of numeric and decimal data types is 38. In earlier versions of SQL Server, the default maximum is 28.
Length for a numeric data type is the number of bytes that are used to store the number. Length for a character string or Unicode data type is the number of characters. The length for binary, varbinary, and image data types is the number of bytes. For example, an int data type can hold 10 digits, is stored in 4 bytes, and does not accept decimal points. The int data type has a precision of 10, a length of 4, and a scale of 0.
When two char, varchar, binary, or varbinary expressions are concatenated, the length of the resulting expression is the sum of the lengths of the two source expressions or 8,000 characters, whichever is less.
When two nchar or nvarchar expressions are concatenated, the length of the resulting expression is the sum of the lengths of the two source expressions or 4,000 characters, whichever is less.
When two expressions of the same data type but different lengths are compared by using UNION, EXCEPT, or INTERSECT, the resulting length is the maximum length of the two expressions.
The precision and scale of the numeric data types besides decimal are fixed. If an arithmetic operator has two expressions of the same type, the result has the same data type with the precision and scale defined for that type. If an operator has two expressions with different numeric data types, the rules of data type precedence define the data type of the result. The result has the precision and scale defined for its data type.
The following table defines how the precision and scale of the result are calculated when the result of an operation is of type decimal. The result is decimal when either of the following is true:
- Both expressions are decimal.
- One expression is decimal and the other is a data type with a lower precedence than decimal.
The operand expressions are denoted as expression e1, with precision p1 and scale s1, and expression e2, with precision p2 and scale s2. The precision and scale for any expression that is not decimal is the precision and scale defined for the data type of the expression.
| Operation | Result precision | Result scale * |
|---|---|---|
| e1 + e2 | max(s1, s2) + max(p1-s1, p2-s2) + 1 | max(s1, s2) |
| e1 - e2 | max(s1, s2) + max(p1-s1, p2-s2) + 1 | max(s1, s2) |
| e1 * e2 | p1 + p2 + 1 | s1 + s2 |
| e1 / e2 | p1 - s1 + s2 + max(6, s1 + p2 + 1) | max(6, s1 + p2 + 1) |
| e1 { UNION | EXCEPT | INTERSECT } e2 | max(s1, s2) + max(p1-s1, p2-s2) | max(s1, s2) |
| e1 % e2 | min(p1-s1, p2 -s2) + max( s1,s2 ) | max(s1, s2) |
* The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated.
The table below shows a simplified mapping between SQL Server, SSIS and Biml data types. The table does not include all possible mappings or all data types, but is meant as a quick reference while developing and learning Biml.
| SQL Server | SSIS Variables | SSIS Pipeline Buffer | OLE DB | ADO.NET | Biml |
|---|---|---|---|---|---|
| bigint | Int64 | DT_I8 | LARGE_INTEGER | Int64 | Int64 |
| binary | Object | DT_BYTES | - | Binary | Binary |
| bit | Boolean | DT_BOOL | VARIANT_BOOL | Boolean | Boolean |
| char | String | DT_STR | VARCHAR | StringFixedLength | AnsiStringFixedLength |
| date | Object | DT_DBDATE | DBDATE | Date | Date |
| datetime | DateTime | DT_DBTIMESTAMP | DATE | DateTime | DateTime |
| datetime2 | Object | DT_DBTIMESTAMP2 | DBTIME2 | DateTime2 | DateTime2 |
| datetimeoffset | Object | DT_DBTIMESTAMPOFFSET | DBTIMESTAMPOFFSET | DateTimeOffset | DateTimeOffset |
| decimal | Decimal | DT_NUMERIC | NUMERIC | Decimal | Decimal |
| float | Double | DT_R8 | FLOAT | Double | Double |
| geography | - | DT_IMAGE | - | Object | Object |
| geometry | - | DT_IMAGE | - | Object | Object |
| hierarchyid | - | DT_BYTES | - | Object | Object |
| image (*) | Object | DT_IMAGE | - | Binary | Binary |
| int | Int32 | DT_I4 | LONG | Int32 | Int32 |
| money | Object | DT_CY, DT_NUMERIC | CURRENCY | Currency | Currency |
| nchar | String | DT_WSTR | NVARCHAR | StringFixedLength | StringFixedLength |
| ntext (*) | String | DT_NTEXT | - | String | String |
| numeric | Decimal | DT_NUMERIC | NUMERIC | Decimal | Decimal |
| nvarchar | String | DT_WSTR | NVARCHAR | String | String |
| nvarchar(max) | Object | DT_NTEXT | - | - | String |
| real | Single | DT_R4 | FLOAT, DOUBLE | Single | Single |
| rowversion | Object | DT_BYTES | - | Binary | Binary |
| smalldatetime | DateTime | DT_DBTIMESTAMP | DATE | DateTime | DateTime |
| smallint | Int16 | DT_I2 | SHORT | Int16 | Int16 |
| smallmoney | Object | DT_CY, DT_NUMERIC | CURRENCY | Currency | Currency |
| sql_variant | Object | DT_WSTR, DT_NTEXT | - | Object | Object |
| table | Object | - | - | - | - |
| text (*) | Object | DT_TEXT | - | - | AnsiString |
| time | Object | DT_DBTIME2 | DBTIME2 | Time | Time |
| timestamp (*) | Object | DT_BYTES | - | Binary | Binary |
| tinyint | Byte | DT_UI1 | BYTE | Byte | Byte |
| uniqueidentifier | String, Object | DT_GUID | GUID | Guid | Guid |
| varbinary | Object | DT_BYTES | - | Binary | Binary |
| varbinary(max) | Object | DT_IMAGE | - | Binary | Binary |
| varchar | String | DT_STR | VARCHAR | String | AnsiString |
| varchar(max) | Object | DT_TEXT | - | - | AnsiString |
| xml | Object | DT_NTEXT | - | - | Xml |
(* These data types will be removed in a future version of SQL Server. Avoid using these data types in new projects, and try to change them in current projects)
| General Type | Type | N value | Precision | Storage size, bytes | Range (in SQL Server) |
|---|---|---|---|---|---|
| Exact Numerics | bit | 1 | 1, 0 | ||
| Exact Numerics | tinyint | 1 | 0 to 255 | ||
| Exact Numerics | smallint | 2 | -2^15(-32768) to 2^15(32767) | ||
| Exact Numerics | int | 4 | -2^31(-2 147 483 648) to 2^31(2 147 483 647) | ||
| Exact Numerics | bigint | 8 | -2^63(-9 233 372 036 854 775 808) to 2^63(9 233 372 036 854 775 807) | ||
| Exact Numerics | decimal | 1-9 10-19 20-28 29-38 |
5 9 13 17 |
from -10^38 +1 through 10^38 -1 | |
| Exact Numerics | smallmoney | 4 | -214 748.3648 to 214 748.3647 | ||
| Exact Numerics | money | 8 | -922 337 203 685 477.5808 to 922 337 203 685 477.5807 | ||
| Approximate Numerics | float | 1-24 25-53 |
7 15 |
4 8 |
-3.40E+38 to -1.18E-38, 0 and 1.18E-38 to 3.40E+38 -1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308 |
| Date and Time | date | 3 | 0001-01-01 through 9999-12-31 January 1, 1 CE through December 31, 9999 CE |
||
| Date and Time | smalldatetime | 4 | 1900-01-01 through 2079-06-06 January 1, 1900 through June 6, 2079 00:00:00 through 23:59:59 |
||
| Date and Time | time | 8-11 12-13 14-16 |
3 4 5 |
00:00:00.0000000 through 23:59:59.9999999 | |
| Date and Time | datetime2 | 1-2 3-4 5-7 |
6 7 8 |
0001-01-01 through 9999-12-31 January 1, 1 CE through December 31, 9999 CE 00:00:00 through 23:59:59.9999999 |
|
| Date and Time | datetime | 8 | anuary 1, 1753 through December 31, 9999 00:00:00 through 23:59:59.997 |
||
| Date and time | datetimeoffset | 26-29 30-34 |
8 10 |
0001-01-01 through 9999-12-31 January 1, 1 CE through December 31, 9999 CE 00:00:00 through 23:59:59.9999999 -14:00 throuth +14:00 |
|
| Character Strings | char | 1-8000 | n | ||
| Character Strings | varchar | 1-8000 | n + 2 | ||
| Character Strings | varchar(max) | 1-(2^31 - 1) | 2^31 - 1 + 2 | ||
| Character Strings | nchar | 1-4000 | |||
| Character Strings | nvarchar | 1-4000 | |||
| Character Strings | nvarchar(max) | 1-(2^31 - 1) | |||
| Character Strings | ntext (*) | 1-(2^30 - 1) | n + n | ||
| Character Strings | text (*) | 1-(2^31 - 1) | |||
| Binary Strings | image (*) | 1-(2^31 - 1) | n | ||
| Binary Strings | binary | 1-8000 | n | ||
| Binary Strings | varbinary | 1-8000 | n | ||
| Binary Strings | varbinary(max) | 1-(2^31 - 1) | n + 2 | ||
| Other Data Types | cursor | ||||
| Other Data Types | sql_variant | max 8016 | |||
| Other Data Types | hierarchyid | max 892 | |||
| Other Data Types | rowversion | 8 | |||
| Other Data Types | timestamp (*) | ||||
| Other Data Types | uniqueidentifier | 16 | |||
| Other Data Types | xml | max 2Gb | |||
| Other Data Types | table | ||||
| Spatial Data Types | geometry | ||||
| Spatial Data Types | geography |
(* These data types will be removed in a future version of SQL Server. Avoid using these data types in new projects, and try to change them in current projects)
- SQL Server 2019 data types
- MySQL 8.0 data types
- Oracle 19C data types
- PostgreSQL 12 data types
- SQLite 3 data types
- SQL Server to MySQL Data Type Conversion
- SQL Server to Oracle Data Type Conversion
- SQL Server to PostgreSQL Data Type Conversion
- SQL Server to SQLite Data Type Conversion
Common data-type conversions between SQL Server, Oracle, Sybase ASE, and DB2. More details here
| Source | Destination |
|---|---|
| MSSQLSERVER | DB2 |
| MSSQLSERVER | ORACLE |
| MSSQLSERVER | SYBASE |
| ORACLE | MSSQLSERVER |
DECLARE @source_dbms SYSNAME = N'%'
, @source_version SYSNAME = N'%'
, @source_type SYSNAME = N'%'
, @destination_dbms SYSNAME = N'%'
, @destination_version SYSNAME = N'%'
, @destination_type SYSNAME = N'%'
, @defaults_only BIT = 0;
SELECT *
FROM sys.fn_helpdatatypemap (
@source_dbms
, @source_version
, @source_type
, @destination_dbms
, @destination_version
, @destination_type
, @defaults_only
);| General Type | Type | ANSI | MySQL | Oracle | PostgreSQL | SQLite |
|---|---|---|---|---|---|---|
| Exact Numerics | bit | No | TINYINT(1) | NUMBER(1) | BOOLEAN | INTEGER |
| Exact Numerics | tinyint | No | TINYINT(3) UNSIGNED | NUMBER(3) | SMALLINT | INTEGER |
| Exact Numerics | smallint | Yes | SMALLINT | NUMBER(5) | SMALLINT | INTEGER |
| Exact Numerics | int | Yes | INT | NUMBER(10) | INT | INTEGER |
| Exact Numerics | bigint | No | BIGINT | NUMBER(19) | BIGINT | INTEGER |
| Exact Numerics | decimal | Yes | DECIMAL | NUMBER(p[,s]) | DECIMAL(p,s) | REAL |
| Exact Numerics | smallmoney | No | DECIMAL(10,4) | NUMBER(10,4) | MONEY | REAL |
| Exact Numerics | money | No | DECIMAL(19,4) | NUMBER(19,4) | MONEY | REAL |
| Approximate Numerics | real | Yes | FLOAT | FLOAT(24) | DOUBLE PRECISION | REAL |
| Approximate Numerics | float(1-24) | Yes | FLOAT | FLOAT(24) | DOUBLE PRECISION | REAL |
| Approximate Numerics | float(25-53) | Yes | FLOAT | FLOAT(49) | DOUBLE PRECISION | REAL |
| Date and Time | date | Yes | DATE | DATE | DATE | TEXT |
| Date and Time | smalldatetime | No | TIMESTAMP | DATE | TIMESTAMP(0) | TEXT |
| Date and Time | time | Yes | TIME | - | TIME | TEXT |
| Date and Time | datetime2 | Yes | DATETIME | - | TIMESTAMP | TEXT |
| Date and Time | datetime | Yes | DATETIME | DATE | TIMESTAMP(3) | TEXT |
| Date and time | datetimeoffset | No | - | - | TIMESTAMP with time zone |
TEXT |
| Character Strings | char | Yes | CHAR | CHAR | CHAR | TEXT |
| Character Strings | varchar | Yes | VARCHAR | VARCHAR2 | VARCHAR | TEXT |
| Character Strings | varchar(max) | Yes | LONGTEXT | VARCHAR2 | TEXT | TEXT |
| Character Strings | nchar | Yes | CHAR with utf8 | NCHAR | CHAR | TEXT |
| Character Strings | nvarchar | Yes | VARCHAR with utf8 | NVARCHAR | VARCHAR | TEXT |
| Character Strings | nvarchar(max) | Yes | LONGTEXT | NCHAR | TEXT | TEXT |
| Character Strings | ntext (*) | No | LONGTEXT | CLOB | TEXT | TEXT |
| Character Strings | text (*) | No | LONGTEXT | LONG | TEXT | TEXT |
| Binary Strings | image (*) | No | LONGBLOB | LONG RAW | BYTEA | BLOB |
| Binary Strings | binary | Yes | BINARY | BLOB | BYTEA | BLOB |
| Binary Strings | varbinary | Yes | VARBINARY | RAW | BYTEA | BLOB |
| Binary Strings | varbinary(max) | Yes | LONGTEXT | RAW | BYTEA | BLOB |
| Other Data Types | cursor | No | - | - | - | - |
| Other Data Types | sql_variant | No | TEXT | CLOB | TEXT | TEXT |
| Other Data Types | hierarchyid | No | TEXT | BLOB | VARCHAR | TEXT |
| Other Data Types | rowversion | No | TEXT | RAW | BYTEA | TEXT |
| Other Data Types | timestamp (*) | No | TEXT | RAW | BYTEA | TEXT |
| Other Data Types | uniqueidentifier | No | CHAR(16) | CHAR(16) | CHAR(16) | TEXT |
| Other Data Types | xml | Yes | LONGTEXT | XMLTYPE | XML | TEXT |
| Other Data Types | table | No | - | - | - | - |
| Spatial Data Types | geometry | No | TEXT | BLOB | VARCHAR | TEXT |
| Spatial Data Types | geography | No | TEXT | BLOB | VARCHAR | TEXT |
(* These data types will be removed in a future version of SQL Server. Avoid using these data types in new projects, and try to change them in current projects)
- Java and SQL Server supported data types
- Data type mappings between Python and SQL Server
- Data type mappings between R and SQL Server
| General Type | Type | ANSI | Java | Python | R |
|---|---|---|---|---|---|
| Exact Numerics | bit | No | boolean | bool | logical |
| Exact Numerics | tinyint | No | short | uint8 | integer |
| Exact Numerics | smallint | Yes | short | int16 | integer |
| Exact Numerics | int | Yes | int | int32 | integer |
| Exact Numerics | bigint | No | long | numeric | numeric |
| Exact Numerics | decimal | Yes | java.math.BigDecimal | ? | numeric |
| Exact Numerics | smallmoney | No | java.math.BigDecimal | ? | numeric |
| Exact Numerics | money | No | java.math.BigDecimal | ? | numeric |
| Approximate Numerics | real | Yes | float | float32 | numeric |
| Approximate Numerics | float(1-24) | Yes | float | float32 | numeric |
| Approximate Numerics | float(25-53) | Yes | double | float64 | numeric |
| Date and Time | date | Yes | java.sql.date | ? | POSIXct |
| Date and Time | smalldatetime | No | java.sql.timestamp | ? | POSIXct |
| Date and Time | time | Yes | ? | ? | POSIXct |
| Date and Time | datetime2 | Yes | java.sql.timestamp | ? | POSIXct |
| Date and Time | datetime | Yes | java.sql.timestamp | ? | POSIXct |
| Date and time | datetimeoffset | No | ? | ? | ? |
| Character Strings | char | Yes | String (**) | str | character |
| Character Strings | varchar | Yes | String (**) | str | character |
| Character Strings | varchar(max) | Yes | String (**) | str | character |
| Character Strings | nchar | Yes | String | str | character |
| Character Strings | nvarchar | Yes | String | str | character |
| Character Strings | nvarchar(max) | Yes | String | str | character |
| Character Strings | ntext (*) | No | String | str | character |
| Character Strings | text (*) | No | String (**) | str | character |
| Binary Strings | image (*) | No | byte[] | str | - |
| Binary Strings | binary | Yes | byte[] | raw | raw |
| Binary Strings | varbinary | Yes | byte[] | bytes | raw |
| Binary Strings | varbinary(max) | Yes | byte[] | bytes | raw |
| Other Data Types | cursor | No | - | - | - |
| Other Data Types | sql_variant | No | String | str | - |
| Other Data Types | hierarchyid | No | String | str | - |
| Other Data Types | rowversion | No | ? | ? | - |
| Other Data Types | timestamp (*) | No | ? | ? | - |
| Other Data Types | uniqueidentifier | No | String | str | - |
| Other Data Types | xml | Yes | ? | ? | - |
| Other Data Types | table | No | - | - | - |
| Spatial Data Types | geometry | No | ? | ? | - |
| Spatial Data Types | geography | No | ? | ? | - |
(* These data types will be removed in a future version of SQL Server. Avoid using these data types in new projects, and try to change them in current projects) (** Only UTF8 Strings supported)
- Data Types (Transact-SQL)
- Data Type Precedence (Transact-SQL)
- Data Type Synonyms (Transact-SQL)
- Precision, Scale, and Length
- Integration Services Data Types
- DbType Enumeration
- SQL Server, SSIS and Biml Data Types
- SQL Server Integration Services, Data Type Mapping
- Data type conversion (Database Engine)