Home » SQL

SQL Data Types

SQL Data Types: In this tutorial, we are going to learn about the various data types with its syntaxes and examples in SQL (Structured Query Language).
Submitted by Abhishek Goel, on March 22, 2020

SQL | Data Types

Just like other programming languages, facilities of defining data of various types are available in SQL also. SQL supports the following data types for the specification of various data-items or fields of a relation/table. In SQL, each column of the table is assigned a datatype which conveys the kind of value that will be stored in the column.

Types of SQL Data Types

1. Numeric data type:
   It includes datatypes like int, tinyint, bigint, float, real, etc.
2. Date and Time data type:
   It includes datatypes like Date, Time, Datetime, etc.
3. Character and String datatype:
   It includes data types like char, varchar, text, etc.
4. Unicode character/string datatypes:
   It includes datatypes like nchar, nvarchar, ntext, etc.
5. Binary datatype:
   It includes datatypes like binary, varbinary, etc.
6. Miscellaneous datatype:
   It includes datatypes like clob, blob, xml, cursor, table, etc.

Many data types are discussed below...

Integer(Numeric)

It stores/represents positive whole number up to 11 digits and negative whole numbers upto 10 digits. The range of integers is from -2,147,483,648 to 2,147,483,647.

Syntax:

    <INTEGER or integer>

SMALLINT

It is a 16-bit signed integer value that stores whole numbers in the range from -32768 to 32767. Its width is up to 5 digits.

Syntax:

    <SMALLINT>

NUMERIC

Numbers are stored in the given format, where x is the total number of digits and y is the number of places to the right of the decimal point. x must include an extra place for the decimal point.

Syntax:

    <NUMERIC(x,y)>

Example:

    Numeric(8,2)

In the given example, numeric datatype stores a number that has 5 places before the decimal and 2 digits after the decimal and 1 digit place for the decimal point. Numeric holds up to 20 significant digits. A negative number holds one place for the sign, i.e.,(-)

DECIMAL

Numbers are stored in the DECIMAL format, where x is the size,i.e., the total number of digits and y is precision, i.e., the number of places to the right of the decimal point.

Syntax:

    <DECIMAL(x,y) or DECIMAL(size,precision)>

Example:

    Decimal(8,2)

In the above example, decimal datatype stores a number that has 5 digits before the decimal and 2 digits after the decimal and 1 digit place for the decimal. Decimal holds up to 19 significant digits. A negative number uses one place for its sign(-).

CHARACTER(fixed length)

This data type stores 'x' number of characters in the string. A maximum of 254 characters can be stored in a string. x or size is the number of characters to store which is of fixed length, to the number of characters specified. If you store strings that are not as long as the 'size' or 'x' parameter value, the remaining spaces are left unused.

Syntax:

    <CHAR(x) or CHAR(size)>

Example:

if you specify CHAR (10), strings such as "ram" and "technology" are each stored as 10 characters. However, a student admission_no is 6 digits long in a school, so CHAR(6) would be appropriate to store the admission_no of all the students. This data type is suitable where the number of characters to store is fixed. The values for CHAR data type have to be enclosed in single or double quotation marks.

CHARACTER (variable length)

This data type is used to store variable-length alphanumeric data.

Syntax:

    <VARCHAR(x) or VARCHAR2(x)>

Example:

The address of a student can be declared as VARCHAR (25) to store the address up to 25 characters long. The advantage of using this data type is that VARCHAR will not leave unused spaces. It releases the unused memory spaces.

DATE

This data type is used to store a date in 'yyyy/mm/dd' format. It stores a year, month and date values. DATE values can be compared with each other only. The date decimal point values to be entered are to be enclosed in { } or with single quotation marks.

Syntax:

    <DATE>

TIME

This data type is used to store time in hh:mm:ss format. It stores hour, minute, and second values.

Syntax:

    <TIME>

For Example, a time of day can be taken as 12:30:45 p.m. where 12 means hours, 30 means minutes and 45 means seconds.

BOOLEAN (logical)

This data type is used for storing logical values, either true or false. In both upper and lower case, T or Y stands for logical true and F or N stands for logical false. The fields with Boolean (logical) datatype can be compared only to other logical columns or constants.

Syntax:

    <BOOLEAN>

BLOB/RAW/LONG RAW

This data type can store data up to a maximum length of 65535 characters. BLOBs are "Binary Large Objects" and are used to store large amounts of data, such as images, animations, clips or other types of files.

Syntax:

    <BLOB or RAW or LONG RAW>

For Example, image raw(2000);

MEMO/LONG

This datatype allows storing characters or remarks upto 2 GB per second.

Syntax:

    <MEMO or LONG>