DATA TYPES IN SQL: SQL Server, Oracle, MySQL, MICROSOFT ACCESS

A Datatype can be defined as “Type of the value that is stored in a column of a table”. For instance, you want to create a table and have a column that will store all the names of students in your class then based on this requirement you choose CHARACTER or STRING datatype. Suppose you want to store all the phone numbers, what would you choose? Yes, right. Integer Datatype!

DATA TYPES IN SQL: SQL Server, Oracle, MySQL, MICROSOFT ACCESS

In this tutorial, you get to learn various SQL Data types that can be used for popular databases such as Oracle, MYSQL, SQL Server and Microsoft Access.

Note: “Not all the data types are compatible with every relational database in existence”. As a SQL developer, you need to know which data types are supported by database you use or refer below to know!!

ORACLE DATATYPES

A simple representation of the data types present in ORACLE Database is shown below.

Character Data types:

These data types are used to store the character (alphanumeric) data in strings whose byte values correspond to an encoding

Also See: Differences between SQL And MySQL

scheme (Character set) like 7 bit ASCII (American Standard Code for Information Interchange), Unicode UTF- 8, EBCDIC (Extended Binary code for Decimal Interchange Code) etc.

DATATYPE	DESCRIPTION
CHAR	It can be used to store fixed-length character Strings. You are required to specify the string length (in bytes or characters) from 1 to 2000 bytes. The default value is 1 byte.
VARCHAR2(SIZE)	It is used to store variable-length character strings. You must specify VARCHAR2(size)    maximum length between 1 to 4000 bytes. Note that if the value you provide exceeds the column’s maximum length, ORACLE database returns an error.
VARCHAR(SIZE)	It is similar to VARCHAR2(size), but this datatype is less used to avoid the possible changes in behavior.
NCHAR(size)	It is a Unicode datatype that stores the Unicode character data. Stores fixed-length character strings corresponding to national character set (AL16UTF16 or UTF-8). Maximum length is 2000 bytes. It can hold up to 2000 characters.
NVARCHAR2(size)	It is also a Unicode datatype that stores the Unicode character data. Stores variable-length character strings corresponding to national character set (AL16UTF16 or UTF-8). Maximum length is 4000 bytes, can hold up to 4000 characters.

Numeric Data types:

These can store positive and negative fixed or floating-point numbers. It also contains storage for zero, positive and negative infinity, and NAN (Not a number or undefined result of an operation).

Also See: TCL Commands in SQL

DATATYPE	DESCRIPTION
NUMBER(p, s)	You can simply specify as NUMBER to store numeric values. Also besides, you can specify NUMBER(p, s) where p is precision(total number of digits) and s is scale (number of digits after the decimal point). It supports precision up to 38 significant digits.
BINARY_FLOAT	It is 32 bit single precision floating point datatype and every BINARY_FLOAT value asks for 5 bytes (including length byte). This is approximate numeric data type which uses binary precision unlike NUMBER data type which uses decimal precision.
BINARY_DOUBLE	It is 64-bit double precision floating point datatype and every such value asks for 9 bytes (including length byte). This also is an approximate numeric data type that uses binary precision.

DATE and TIME Data types:

DATATYPE	DESCRIPTION
DATE	It can be used to store the year, month, day, hours, minutes, and seconds. The standard format is ‘DD-MM-YY’ but this can be altered by using the parameter ‘NLS_DATE_FORMAT’ or TO_DATE function can also be used with a format mask. Can store dates ranging from ’01-01-4712′ BCE to ’31-12-9999′ CE (or AD).
TIMESTAMP	It is used to store the valid date along with time in 24-hour format ‘HH:MM: SS’. The default value for time is 00:00:00 A.M.

Larger Object (LOB) Data types:

Large unstructured data such as video clips, sound waves, texts, graphic images can be stored and manipulated in binary or character format. LOBs are always preferred over LONG datatype.

DATATYPE	DESCRIPTION
BLOB	It can be used to specify unstructured binary data in the database which can store up to 128 terabytes of binary data.
CLOB	It can be used to store up to 128 terabytes of character data in the database. More specifically, they are used to store database character set data.
NCLOB	It can be used to store up to 128 terabytes of character data in the database. Unlike CLOB, this stores Unicode national character set data.
BFILE	This is a unique datatype that stores the unstructured binary data in OS – operating system files that are external to database. The column/attribute defined with this datatype stores the file locator which in turn points to an external file containing data. It is important to note that the amount of BFILE data that can be stored depends upon the operating system.
LONGRAW	This datatype provides storage for binary data, graphics, documents supports backward compatibility with existing applications. For newer applications, BLOB and BFILE are always recommended over LONGRAW.
RAW(size)	RAW is a variable-length datatype similar to VARCHAR2 character datatype. This can be indexed, unlike LONGRAW.

ROWID Datatype:

DATATYPE	DESCRIPTION
ROWID	This helps us to store the row’s ID that is the address of every row in the database.
UROWID	This is just a universal rowid datatype that supports all kinds of rowids.

MYSQL DATATYPES:

A simple representation of the data types present for MYSQL Database is shown below.

Numeric Datatypes:

It includes exact numeric data types (TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT) and approximate numeric data types (FLOAT, DOUBLE).

DATATYPE	DESCRIPTION
BIT(N)	It can be used to store bit values. Suppose you write BIT (N) – this enables storage of N bit values were N ranges from 1 to 64. To specify bit values you can mention b’value’ (without space) notation where value is binary value (written using zeroes and ones).
TINYINT(size)	A very small integer which stores up to 1 byte. Signed value ranges from -128 to 127. Unsigned value ranges from 0 to 255.
SMALLINT(size)	A small integer that stores up to 2 bytes. Signed value ranges from -32768 to 32767. Unsigned value ranges from 0 to 65535.
MEDIUMINT(size)	A medium integer that stores up to 3 bytes. Signed value ranges from -8388608 to 8388607. Unsigned value ranges from 0 to 16777215.
INT(size) or INTEGER(size)	It is used for the standard integer value and stores up to 4 bytes. Signed value ranges from -2147483648 to 2147483647. Unsigned value ranges from 0 to 4294967295.
BIGINT(size)	It is used for the big integer value and stores up to 8 bytes. Signed value ranges from -263 to 263-1. Unsigned value ranges from 0 to 264-1.
DECIMAL(precision, scale)	It can be used to specify fixed point number where precision specifies the total number of digits and scale is number of digits after the decimal point. The maximum number of digits for decimal is 65 but actual range for a column can be constrained by given precision and scale.
FLOAT(p)	It can contain approximate numeric data types. It uses 4 bytes for single precision. The letter ‘p’ indicates precision. Note that p ranging from 0 to 23 results in 4-byte single precision Float column.
DOUBLE(p, s)	By now, we know that ‘p’ is precision and ‘s’ is scale. It uses 8 bytes for double precision. Note that p ranging from 24 to 53 results in 8-byte double precision Double column.
DEC(p, s)	It is equivalent to DECIMAL(p, s).

Boolean Data type:

DATATYPE	DESCRIPTION
BOOLEAN	Since MySQL does not have an inbuilt keyword ‘BOOLEAN’, we use TINYINT(1) to represent Boolean values.

Also See: Data Definition language In SQL

String Data types:

DATATYPE	DESCRIPTION
CHAR(size)	These are fixed-length strings where size specifies the maximum number of characters that you want to store. The size can vary from 0 to 255. When values are stored with this datatype, they are right padded with spaces to the specified length and when they are retrieved, trailing spaces are removed by default (Unless you enable PAD_CHAR_TO_FULL_LENGTH mode).
VARCHAR(size)	These are variable-length strings where size specifies the maximum number of characters that you want to store. The size can vary from 0 to 65,535. Unlike CHAR, when these are stored they are not right padded with spaces to the specified length and on retrieving those trailing spaces are retained.
BINARY(size)	Similar to CHAR(size), but stores binary strings rather than characters. The maximum length possible is same as that of CHAR except that length is measured in bytes.
VARBINARY(size)	Similar to VARCHAR(size), but stores binary strings. Maximum length possible is same as VARCHAR except that length is measured in bytes.
TINYBLOB	It can hold a variable amount of data and maximum length supported is 255 bytes.
TINYTEXT	It is used to store a string with a maximum length of 255 characters.
TEXT(size)	It is used to store a string with a maximum length of 65,535 bytes.
BLOB(size)	It is used for BLOBs (Binary Large Objects) and can store up to 65,535 bytes of data.
MEDIUMTEXT	It is used to store a string with a maximum length of 16,777,215 characters.
MEDIUMBLOB	It is used for BLOBs (Binary Large Objects) and can store up to 16,777,215 bytes of data.
LONGTEXT	It is used to store a string with a maximum length of 4,294,967,295 characters
LONGBLOB	It is also used for BLOBs (Binary Large Objects) and can store up to maximum 4,294,967,295 bytes of data.
ENUM(v1, v2, v3. )	This is a string object that consists of one value chosen from a list of allowed values. In this case – v1, v2, v3 are the permissible values. Totally, 65535 distinct values can present in the ENUM list. The elements present in the list are assigned with index value starting from 1.
SET(v1, v2, v3… )	This is also a string object that can have zero or more values provided each of which must be selected from a list of possible values specified while creating a table. In this case – v1, v2, v3 are the permissible values. In total, SET can hold up to 64 distinct members.

Date and Time Data types:

DATATYPE	DESCRIPTION
DATE	The Date format is ‘YYYY-MM-DD’ and the range starts from ‘1000-01-01’ to ‘9999-12-31’.
DATETIME(fsp)	This datatype is a date and time combination. Format is ‘YYYY-MM-DD hh:mm:ss’. And the range starts from ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’. In this case, fsp indicates a value in the range from 0 to 6 given to specify fractional seconds precision. A value of 0 indicates that there is no fractional part.
TIMESTAMP(fsp)	This is a timestamp. TIMESTAMP values are stored as the number of seconds. Format is ‘YYYY-MM-DD hh:mm:ss’. And the range is from ‘1970-01-01 00:00:01’ UTC to ‘2038-01-09 03:14:07’ UTC.
TIME(fsp)	A time. Format is ‘hh:mm:ss’. Range varies from ‘-838:59:59.000000’ to ‘838:59:59.000000’.
YEAR[(4)]	A year in four-digit format ‘YYYY’. Allows the values to the YEAR column using either strings or numbers. Values allowed in the four-digit format are from 1901 to 2155 or 0000.

Spatial Data types:

DATATYPE	DESCRIPTION
GEOMETRY	It is a spatial value of any type.
POINT	Represents a single location in a coordinate space (a pair of X-Y coordinates).
CURVE	It is a one-dimensional geometry represented by the sequence of points.
LINESTRING	This is also a curve with linear interpolation of points.
SURFACE	It is a two-dimensional property whose subclass is a polygon.
POLYGON	A planar surface usually.
GEOMETRYCOLLECTION	A collection of zero or more GEOMETRY class or those having same coordinate system.
MULTILINESTRING	A collection of LINESTRING values.
MULTIPOINT	Zero dimensional. A collection of POINT values where points are not connected or ordered in any way.
MULTISURFCACE	A collection of surface elements values
MULTICURVE	Collection of curve elements.

JSON Data types:

DATATYPE	DESCRIPTION
JSON	This is a native JSON datatype that provides efficient access to data in JavaScript Object Notation (JSON) documents.

SQL SERVER DATA TYPES

A simple representation of the data types present in SQL SERVER Database is shown below.

Also See: DBMS Transactions

Numeric Data types:

DATATYPE	DESCRIPTION
BIT	Possible values here are integers (0, 1 or, null).
TINYINT	Storage is 1 byte and allows whole numbers from 0 to 255.
SMALLINT	Storage is 2 bytes and the range allowed is between -215 to 215.
INT	Storage is 4 bytes and the range allowed is between -231 to 231-1.
BIGINT	Storage is 8 bytes and the range allowed is between -263 to 263-1
FLOAT(N)	It can be used to specify floating precision number data ranging from -1.79E+308 to 1.79E+308. The values of ‘N’ can be from 1 to 53 and the default value is 53. The ‘N’ parameter decides if the field can hold the 4 or 8 bytes which means if ‘N’ is between 1 to 24 then precision is 7 digits and stores up to 4 bytes or if ‘N’ is between 25 to 53 then precision is 15 digits and stores up to 8 bytes.
REAL	It is a floating precision number data from -3.40E+38 to 3.40E+38 and storage is up to 4 bytes.
SMALLMONEY	The range is up to -214,748.3648 to 214,748.3647 and storage is up to 4 bytes.
MONEY	It is used to specify monetary data from -922,337,233,685,477.5808 to 922,337,203,685,477.5807 and storage is up to 8 bytes.

Character and Unicode Character string Data types:

DATATYPE	DESCRIPTION
CHAR(N)	It is a fixed size string character data type and storage is up to 8000 characters.
VARCHAR(N)	It is a variable width character string data type and storage is up to 8000 characters where ‘N’ defines string size in bytes.
TEXT	It can be used to store variable-length non-Unicode data. Its size can be up to 2GB of text data.
NTEXT	It is used to store variable-length Unicode data with a maximum string length of 230 – 1 (1,073,741,823) bytes.
NCHAR	It is a fixed-width Unicode string data type. Its size can be up to 4000 characters.
NVARCHAR	It is a variable-width Unicode string data type. Its size can be up to 4000 characters.
IMAGE	It is used to store variable-length binary data from 0 through 231-1 (2,147,483,647) bytes

Also See: Data Models in DBMS

Binary String data types:

DATATYPE	DESCRIPTION
BINARY(N)	It is a fixed-width Binary string data type and can store up to 8000 bytes.
VARBINARY	It is a variable-width Binary string data type and can store up to 8000 bytes.

Date and Time Data types:

DATATYPE	DESCRIPTION
DATE	It is used to store date, default format ‘YYYY-MM-DD’ and ranges from ‘0001-01- 01’ to ‘9999-12-31’.
DATETIME	It is used to specify the date and time together. Date range is ‘January 1, 1753’ to ‘December 31, 9999′. Time range is ’00:00:00 to 23:59:59.997’. Storage is up to 8 bytes.
DATETIME2	This is another datatype to specify the date and time together. Date range is ‘0001-01- 01’ through ‘9999-12-31′. Time range is ’00:00:00′ to ’23:59:59.9999999’. Accuracy is around 100ns.
TIME	It stores time in format ‘hh:mm:ss[.nnnnnnn]’ where ‘hh’ is hour – two digits, ranging from 0 to 23, ‘mm’ is minutes – two digits, ranging from 0 to 59, ‘ss’ is second two digits, ranging from 0 to 59.
TIMESTAMP	It represents automatically generated values which are unique in the database, every time a row is changed, this value is increased.

Others:

DATATYPE	DESCRIPTION
UNIQUEIDENTIFIER	It can be used to store GUID (globally unique identifier) which uses NEWID( )or NEWSEQUENTIALID( ) functions in order create a unique identifier.
CURSOR	This can be used for variables or store procedure output parameters which contain a reference to the cursor in database operations.
SQL_VARIANT	This can be used to store the values of different SQL supported data types and can have a maximum length of 8016 bytes.
XML	It stores XML formatted data. XML instances can be stored in a column/variable of XML type and a maximum of 2gb is allowed for XML datatype instances.
ROWVERSION	This datatype automatically generates a unique number in a database and storage up to 8 bytes.
TABLE	The table datatype can be used for temporarily storing result for processing at a later time. For example – functions, procedures, and batches use table variables that can return table-valued result set.

Also See: Various Keys in Database Management System (DBMS)

MICROSOFT ACCESS DATA TYPES:

A simple representation of the data types present in the MICROSOFT ACCESS Database is shown below.

Common / Simple Data types:

DATATYPE	DESCRIPTION
SHORTTEXT	It can be used to store simple text or combination of text and numbers (Alphanumeric data) which can store up to 255 characters.
LONGTEXT	It is used when more storage is required and lengthier version of alphanumeric data has to be stored. Stores up to 63,999 characters.
NUMBER	It is a numeric datatype that stores only numbers.
SINGLE	It is a Single precision floating-point datatype which handles decimals.
DOUBLE	It is a Double-precision floating-point datatype which handles decimals.
CURRENCY	It is used for currency which stores up to 15 digits whole numbers and provides 1 to 4 decimal places.
AUTONUMBER	When a column is defined with this datatype, the MS ACESS database automatically generates each record with a unique number usually starting from 1.
DATE/TIME	It is used for dates and time ranges from 1000 to 9999 and stores up to 8 bytes.
YES/NO	Values allowed in this column are only one among YES/NO. (True/False or On/Off can also be used). Storage is of 1 bit.

Also See: Different Types of Database Users in DBMS

Advanced Data types:

DATATYPE	DESCRIPTION
OLE OBJECT	It is used to store pictures, audios, videos, and other BLOBs (Binary Large Objects) whose storage is up to 2GB.
HYPERLINK	It stores texts and numbers in a combination that forms the links/hyperlink address to other files or web pages.
LOOKUP WIZARD	This allows you to define either a simple or complex lookup field. The difference being the first uses contents of another table or uses a value list to store a single value in each row and later one allows storing multiple values of the same datatype in each of the row.
ATTACHMENT	It can be used to store multiple files per record. Also, it can store digital photos. This data type is new and available only in later versions of the MS ACCESS database whose storage is up to 2GB.

Also See: SQL Server CONSTRAINTS With Examples

So it was all about SQL Data Types, If you liked them then please share them with your friends or if you have any query then please comment below.