SQL Server datatypes
Datatype conversion is one of the key issues when convert one dialect of SQL to another. This article documents SQL Server datatypes and how to convert it to corresponding datatype of other databases.
SQL Server supplies a set of system data types that define all the types of data that can be used with SQL Server. You can also define your own data types in Transact-SQL. Data types in SQL Server are organized into the following categories: Character strings, Unicode character strings, Binary strings, Exact numerics, Approximate numerics, Date and time, Other data types.
Character strings
- char[(n)]
- Description: Fixed-length, non-Unicode character data with a length of n bytes. n must be a value from 1 through 8,000.
- ISO synonym: character
- Oracle: char[(size)], 1 <= size <= 2,000 bytes
- varchar [ ( n | max ) ]
- Description: Variable-length, non-Unicode character data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes.
- ISO synonym: character varying, char varying
- Oracle: varchar2(n), or clob (n>4000)
- text
- Description: Variable-length non-Unicode data in the code page of the server and with a maximum length of 2^31-1 (2,147,483,647) characters. When the server code page uses double-byte characters, the storage is still 2,147,483,647 bytes. Depending on the character string, the storage size may be less than 2,147,483,647 bytes.
Will be removed in a future version of Microsoft SQL Server, use varchar(max) instead. - ISO synonym: N/A
- Oracle: clob
- Description: Variable-length non-Unicode data in the code page of the server and with a maximum length of 2^31-1 (2,147,483,647) characters. When the server code page uses double-byte characters, the storage is still 2,147,483,647 bytes. Depending on the character string, the storage size may be less than 2,147,483,647 bytes.
Unicode Character strings
- nchar[(n)]
- Description: Fixed-length Unicode character data of n characters. n must be a value from 1 through 4,000. The storage size is two times n bytes.
- ISO synonym: national char, national character
- Oracle: nchar[(n)], 1 <= n <= 2,000 characters; nclob, n > 2000
- nvarchar [ ( n | max ) ]
- Description:Variable-length Unicode character data. n can be a value from 1 through 4,000 characters. max indicates that the maximum storage size is 2^31-1 bytes. The storage size, in bytes, is two times the number of characters entered + 2 bytes.
- ISO synonym: national character varying, national char varying
- Oracle: varchar2(n), or clob (n>4000)
- ntext
- Description: Variable-length Unicode data with a maximum length of 2^30 – 1 (1,073,741,823) characters. Storage size, in bytes, is two times the number of characters entered.
Will be removed in a future version of Microsoft SQL Server, use nvarchar(max) instead. - ISO synonym: national text
- Oracle: nclob
- Description: Variable-length Unicode data with a maximum length of 2^30 – 1 (1,073,741,823) characters. Storage size, in bytes, is two times the number of characters entered.
Binary strings
- binary [ ( n ) ]
- Description: Fixed-length binary data with a length of n bytes, where n is a value from 1 through 8,000. The storage size is n bytes.
- ISO synonym: N/A
- Oracle:raw(1)(n was omitted); raw(n), 1<=n<=2000;blob(n>2000)
- varbinary [ ( n | max) ]
- Description:Variable-length binary data. n can be a value from 1 through 8,000 bytes. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of the data entered + 2 bytes.
- ISO synonym:binary varying
- Oracle: raw(1)(n was omitted); raw(n), 1<=n<=2000;blob(n>2000)
- image
- Description: Variable-length binary data from 0 through 2^31-1 (2,147,483,647) bytes.
Will be removed in a future version of Microsoft SQL Server, use varbinary(max) instead. - ISO synonym: N/A
- Oracle: blob
- Description: Variable-length binary data from 0 through 2^31-1 (2,147,483,647) bytes.
Numerics
- bigint
- Description: 8 bytes, The bigint data type is intended for use when integer values might exceed the range that is supported by the int data type.
- ISO synonym: N/A
- Oracle:NUMBER(19)
- int
- Description: 4 bytes, The int data type is the primary integer data type in SQL Server.
- ISO synonym: N/A
- Oracle:NUMBER(10)
- smallint
- Description: 2 bytes
- ISO synonym: N/A
- Oracle: NUMBER(5)
- tinyint
- Description: 1 byte
- ISO synonym: N/A
- Oracle: NUMBER(3)
- bit
- Description: An integer data type that can take a value of 1, 0, or NULL.
- ISO synonym: N/A
- Oracle: NUMBER(3)
- numeric[(p[,s])], decimal[(p[,s])]
- Description: Numeric data types that have fixed precision and scale.
- ISO synonym: dec(p, s)
- Oracle:number[(p[,s])]
- money
- Description:Data types that represent monetary or currency values. The money and smallmoney data types are accurate to a ten-thousandth of the monetary units that they represent.
- ISO synonym: N/A
- Oracle:number(19,4)
- smallmoney
- Description:Data types that represent monetary or currency values. The money and smallmoney data types are accurate to a ten-thousandth of the monetary units that they represent.
- ISO synonym: N/A
- Oracle:number(10,4)
- float[(n)]
- Description:Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly.
- ISO synonym: float[(n)], for n = 8-15
- Oracle:float(49)
- real
- Description:
- ISO synonym: float[(n)], for n = 1-7
- Oracle:float(23)
Date and Time
- Date
- Description: Defines a date.
- ISO synonym:
- Oracle:date
- Datetime
- Description: Defines a date that is combined with a time of day with fractional seconds that is based on a 24-hour clock.
- ISO synonym:
- Oracle:date, Fractional parts of a second are truncated
- Datetime2
- Description:Defines a date that is combined with a time of day that is based on 24-hour clock. datetime2 can be considered as an extension of the existing datetime type that has a larger date range, a larger default fractional precision, and optional user-specified precision.
- ISO synonym:
- Oracle:timestamp
- Datetimeoffset
- Description: Defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock.
- ISO synonym:
- Oracle:timestamp with time zone
- Smalldatetime
- Description:Defines a date that is combined with a time of day. The time is based on a 24-hour day, with seconds always zero (:00) and without fractional seconds.
- ISO synonym:
- Oracle:date, The value for seconds is returned as 0
- time
- Description: Defines a time of a day. The time is without time zone awareness and is based on a 24-hour clock.
- ISO synonym:
- Oracle:timestamp
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.
| ANSI SQL Synonym | SQL Server 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.
[…] information about Oracle datatypes and SQL Server datatypes: including datatype name, description and what’s the corresponding datatype in other […]