SQL Data Types: In this tutorial, you will learn about the different data types that can be stored in a SQL database. It is important to know each data type of SQL Server because the data type of a column defines which type of value the column can hold.
The main categories of data types include character string types, numeric types, and date/time types.
The SQL Data types are categorized into mainly the following data types:
- Numeric data types include INT, TINYINT, BIGINT, FLOAT, REAL, etc.
- Date and Time data types include DATE, TIME, DATETIME, etc.
- Character and String data types include CHAR, VARCHAR, TEXT, etc.
You can also refer to the below image to get a clear idea of various SQL data types:
We will discuss all the above SQL data types categories in detail.
Relational Database Vendor Differences
When designing a database schema or writing SQL queries, it’s important to check if the data types you want to use are supported. For example, the Oracle database doesn’t support DATETIME, and MySQL doesn’t support CLOB.
Below we mentioned only those data types which are commonly used. Before creating a database, check the maximum size limit for different data types with your relational database vendor to avoid issues.
Note: Every relational database vendor supports not all data types.
SQL Data Types Numeric
Numeric data types include INT, FLOAT, and DECIMAL. These types store numerical values, such as prices or quantities.
The table below contains a list of all the numeric datatypes in SQL and descriptions for each.
| Data Type | Description |
|---|---|
| BIT(SIZE) | A bit-value type with a default of 1. The allowed number of bits in a value is set via the size parameter, which can hold values from 1 to 64. |
| TINYINT(SIZE) | A very small integer with a signed range of -128 to 127 and an unsigned range of 0 to 255. Here, the size parameter specifies the maximum allowed display width, 255. |
| BOOLEAN | Same as BOOL |
| SMALLINT(size) | A small integer with a signed range of -32768 to 32767 and an unsigned range from 0 to 65535. Here, the size parameter specifies the maximum allowed display width, 255. |
| MEDIUMINT(size) | A medium integer with a signed range of -8388608 to 8388607 and an unsigned range from 0 to 16777215. Here, the size parameter specifies the maximum allowed display width of 255. |
| INT(size) | A medium integer with a signed range of -2147483648 to 2147483647 and an unsigned range from 0 to 4294967295. Here, the size parameter specifies the maximum allowed display width, 255. |
| INTEGER(size) | Same as INT. |
| BIGINT(size) | A medium integer with a signed range of -9223372036854775808 to 9223372036854775807 and an unsigned range from 0 to 18446744073709551615. Here, the size parameter specifies the maximum allowed display width, 255. |
| FLOAT(p) | A floating point number value. Suppose the precision ( p ) parameter is between O to 24. then the data type is set to FLOAT ( ), while if it’s from 25 to 53, it is set to DOUBLE ( ). This behavior is to make the storage of values more efficient. |
| DOUBLE (size, d) | A floating point number value where the size parameter sets the total digits and the number of digits after the decimal point is set by the d parameter. |
| DECIMAL (size, d) | An exact fixed point number where the size parameters set the total number of digits, and the total number of digits after the decimal point is set by the d parameter. |
| DEC (size, d) | For size, the maximum number is 65, and the default is 10, while for d, the maximum number is 30, and the default is 10. Same as DECIMAL |
SQL Data Types String
The table below lists all the String type datatypes available in SQL and descriptions that may help understand their use.
| Data Type | Description |
|---|---|
| CHAR(SIZE) | Fixed length string which can contain letters, numbers, and special characters. The size parameter sets the maximum string length from 0-255 with a default of 1. |
| VARCHAR(SIZE) | Variable length string similar to CHAR. But with a maximum string length range from 0 to 65535. |
| BINARY(SIZE) | Similar to CHAR but stores binary byte strings. |
| VARCHAR(SIZE) | Similar to CHAR but stores binary byte strings. |
| TINYBLOB | Holds Binary Large Objects ( BLOBS ) with a max length of 255 bytes. |
| TINYTEXT | Holds a string with a maximum length of 255 characters. Use VARCHAR( ) instead, as it’s fetched much faster. |
| TEXT(SIZE) | Holds a string with a maximum length of 65535 bytes. Again, better to use VARCHAR. |
| BLOB(SIZE) | Holds Binary Large Objects ( BLOBS ) with a max length of 65535 bytes. |
| MEDIUMBLOB | Holds Binary Large Objects ( BLOBS ) with a max length of 16,777,215 bytes |
| LONGTEXT | Holds a string with a maximum length of 4,294,967,295 characters. |
| LONGBLOB | Holds Binary Large Objects ( BLOBS ) with a max length of 4,294,967,295 bytes |
| ENUM(a, b, c, etc) | A string object with only one value is chosen from a list of values you define, up to a maximum of 65535. If a value not on this list is added, it’s replaced with a blank value. Think of ENUM being similar to HTML radio boxes in this regard. CREATE TABLE tshirts ( color ENUM ‘red’, ‘green ‘, ‘blue’, ‘yellow’, ‘purple’ ) ) ; |
| SET (a, b, c, etc.) | A string object can have 0 or more values, which are chosen from a list of values you define, up to a maximum of 64 values. Think of SET being similar to HTML checkboxes in this regard. |
SQL Data Types For Date
.The following table lists the available Date/Time datatypes in SQL, along with a description:
| Data Types | Description |
|---|---|
| DATE | A simple date in YYYY – MM – DD format, with a supported range from ‘1000-01-01’ to ‘9999-12-31’. |
| DATETIME(FSP) | A date time in YYYY – MM – DD hh : mm: ss format, with a supported range from ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59′.’ Adding DEFAULT and ON UPDATE to the column definition automatically sets the current date/time. |
| TIMESTAMP(FSP) | A Unix Timestamp is a value relative to the number of seconds since the Unix epoch ( 1970-01-01 00:00:00 ‘ UTC ). This has a supported range from ‘1970-01-01 00:00:01’ UTC to ‘2038-01-09 03:14:07’ UTC Adding DEFAULT CURRENT TIMESTAMP and ON UPDATE CURRENT TIMESTAMP to the column definition automatically sets it to the current date/time. |
| TIME(FSP) | A time in hh: mm: ss format, with a supported range from ‘838: 59: 59’ to ‘ 838: 59: 59’. |
| YEAR | A year, with a supported range of ‘1901’ to ‘2155’. |
Conclusion:
After going through This blog post about SQL Data types, we believe you know different Data type categories like SQL Data Types Numeric, SQL Data Types String &SQL Data Types For Date. But if you want to know more about SQL SERVER or want to master SQL Server, then you need to check our sql tutorial point post, where we have shared all the tutorials on SQL.
If you have any questions about implementing the SQL Server data types, you can post your questions in the comment section, and we will try to answer those as soon as possible.
What Are The Sql Data Types?
A few different SQL data types are used to store data in a database. The most commonly used data type is the text data type, which can be used to store any data. Other common SQL data types include integers, floating-point numbers, and dates.
What Is Varchar Data Type?
VARCHAR is a data type in SQL that stores character string data. VARCHAR stands for “Variable Character” because it can store strings of variable lengths. The maximum length of a VARCHAR column depends on the database management system (DBMS) being used, but typically it can store up to 65,535 characters.
What are the 5 main data types?
The five main data types recognized by current computer languages are Integral, Floating Point, Character, String of Characters, and Composite. Each wide selection has various subtypes defined within it.
