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 |
| TIMESTAMP | DATETIME field in YYYYMMDDHHMMSS format. |
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 |