Microsoft SQL Server Data Types

Detailed information about Microsoft SQL Server Data Types and its mapping to another databases and program languages analog.

Table of Contents:

Data Type Precedence (Transact-SQL)

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:

  1. sql_variant (highest)
  2. xml
  3. datetimeoffset
  4. datetime2
  5. datetime
  6. smalldatetime
  7. date
  8. time
  9. float
  10. real
  11. decimal
  12. money
  13. smallmoney
  14. bigint
  15. int
  16. smallint
  17. tinyint
  18. bit
  19. ntext
  20. text
  21. image
  22. timestamp
  23. uniqueidentifier
  24. nvarchar (including nvarchar(max))
  25. nchar
  26. varchar (including varchar(max))
  27. char
  28. varbinary (including varbinary(max))
  29. binary (lowest)

⬆ back to top

Data Type Synonyms (Transact-SQL)

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.

⬆ back to top

Precision, Scale, and Length (Transact-SQL)

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.

⬆ back to top

SQL Server, SSIS and Biml Data Types

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)

⬆ back to top

SQL Server Data Types Length

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)

⬆ back to top

SQL Server to MySQL, Oracle, PostgreSQL, SQLite Data Type Mapping

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)

⬆ back to top

SQL Server Data Types Mapping to Program Languages

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)

⬆ back to top

Source links

⬆ back to top