Data Types in SQL With Examples

In this post, we will discuss on the some of the most important and frequently used data types available in SQL. Rather than discussing all the data types at once, we will divide the data types into 4 categories.

Oracle, MySQL, SQL Server, SQLite, sybase etc is few of the database management systems available. Not all the data types mentioned below will be supported by all the Database management system. For example, MONEY and SMALL MONEY are supported only by Microsoft’s SQL Server. Oracle database does not support DATETIME and MySQL doesn’t support CLOB data type.

Every relational database vendor has it’s own maximum size limit for different data types.

Numeric Data types

Data Types Description
INT We can store numeric values for columns declared as INT data type,
RANGE: -2,147,483,648 to 2,147,483,647.
SMALLINT Same like INT we can store numeric values in the below range.
RANGE: -32,768 to 32,767
TINYINT This data type has a range lower than SMALLINT.
RANGE: 0 to 255.
BIGINT This data type has the highest range of values.
RANGE: -9,223,372,036, 854,775,808 to 9,223,372,036, 854,775,807
BIT This data type is mostly used for decision related condition. Like for evaluating true or false. 1 considered as true and 0 considered as false.
RANGE: 0 to 1
DOUBLE For the column declared as DOUBLE data type, we can store double precision floating-point values.
DECIMAL For the column declared as DECIMAL data type, we can store floating values i.e. numbers which have decimal points included in it.
RANGE: – 1038 + 1 to 1038 – 1
MONEY It is mostly preferred to use DECIMAL instead of MONEY data type because if its lack in preciseness.
RANGE: -922,337,203,685,477.5808 to 922,337,203,685,477.5808
SMALLMONEY RANGE: -214,748.3648 to 214,748.3648

Date and Time Data types

Data Types Description
DATE As the name suggests, any Date values can be stored in the column declared with DATE data type. And the format of the date will be YYYY-MM-DD format.
TIME Time values with HH:MI:SS format can be stored in the column declared with TIME data type.
DATETIME If you want to store a value that has both date and time values, then we can make use of this data type.
Format that can be stored in the column is YYYY-MI-DD HH:MM:SS

Character and String Data types.

Data Types Description
CHAR The values with maximum size of 8000 characters and a fixed length can be stored.
VARCHAR The values with maximum size of 8000 characters and a variable length can be stored
TEXT This data type has advantage over CHAR and VARCHAR. Maximum size of 2,147,483,647 characters can be stored.

Other Data type

These are used for storing very large data into the table like images, files etc.

Data type Description
JSON Column with JSON data type is used to stored JSON data.
XML As opposed to JSON, XML data typed column can be used to store XML data.
CLOB Object up to 2GB can be stored.
BLOB BLOB stands for Binary Large Objects & hence it is used for storing large objects

Leave a Reply