Answer»
To allow the users to work with tables effectively, SQL provides us with various datatypes each of which can be useful based on the type of data we handle.
The above image is a chart that shows all the datatypes available in SQL along with some of their examples. The next section describes various most popular SQL server datatypes categorised under each major division.
String Datatypes:The table below lists all the String type datatypes available in SQL, along with their descriptions:
| Datatype | Description |
|---|
| CHAR(size) | A fixed-length string containing numbers, letters or special characters. Length may vary from 0-255. |
|---|
| VARCHAR(size) | Variable-length string where the length may vary from 0-65535. Similar to CHAR. |
|---|
| TEXT(size) | Can contain a string of size up to 65536 bytes. |
|---|
| TINY TEXT | Can contain a string of up to 255 characters. |
|---|
| MEDIUM TEXT | Can contain a string of up to 16777215 characters. |
|---|
| LONG TEXT | Can contain a string of up to 4294967295 characters. |
|---|
| BINARY(size) | Similar to CHAR() but stores binary byte strings. |
|---|
| VARBINARY(size) | Similar to VARCHAR() but stores binary byte strings. |
|---|
| BLOB(size) | Holds blobs up to 65536 bytes. |
|---|
| TINYBLOB | It is used for Binary Large Objects and has a maximum size of 255bytes. |
|---|
| MEDIUMBLOB | Holds blobs up to 16777215 bytes. |
|---|
| LONGBLOB | Holds blobs upto 4294967295 bytes. |
|---|
| ENUM(val1,val2,…) | String object that can have only 1 possible value from a list of size at most 65536 values in an ENUM list. If no value is inserted, a blank value is inserted. |
|---|
| SET(val1,val2,…) | String object with 0 or more values, chosen from a list of possible values with a maximum limit of 64 values. |
|---|
Numeric Datatypes:The table below lists all the Numeric Datatypes in SQL along with their descriptions:
| Datatype | Description |
|---|
| BIT(size) | Bit-value type, where size varies from 1 to 64. Default value: 1 |
|---|
| INT(size) | Integer with values in the signed range of -2147483648 to 2147483647 and values in the unsigned range of 0 to 4294967295. |
|---|
| TINYINT(size) | Integer with values in the signed range of -128 to 127 and values in the unsigned range of 0 to 255. |
|---|
| SMALLINT(size) | Integer with values in the signed range of -32768 to 32767 and values in the unsigned range of 0 to 65535. |
|---|
| MEDIUMINT(size) | Integer with values in the signed range of -8388608 to 8388607 and values in the unsigned range of 0 to 16777215. |
|---|
| BIGINT(size) | Integer with values in the signed range of 9223372036854775808 to 9223372036854775807 and values in the unsigned range of 0 to 18446744073709551615. |
|---|
| BOOLEAN | Boolean values where 0 is considered as FALSE and non-zero values are considered TRUE. |
|---|
| FLOAT (p) | The floating-point number is stored. If the precision parameter is set between 0 to 24, the type is FLOAT() else if it lies between 25 to 53, the datatype is DOUBLE(). |
|---|
| DECIMAL(size,d) | Decimal number with a number of digits before decimal place set by size parameter, and a number of digits after the decimal point set by d parameter. Default values: size = 10, d = 10. Maximum Values: size = 65, d = 30. |
|---|
Date/Time Datatypes:The datatypes available in SQL to handle Date/Time operations effectively are called the Date/Time datatypes. The below table lists all the Date/Time variables in SQL along with their description:
| Datatype | Description |
|---|
| DATE | Stores date in YYYY-MM-DD format with dates in the range of ‘1000-01-01’ to ‘9999-12-31’. |
|---|
| TIME(fsp) | Stores time in hh:mm:ss format with times in the range of ‘-838:59:59’ to ‘838:59:59’. |
|---|
| DATETIME(fsp) | Stores a combination of date and time in YYYY-MM-DD and hh:mm:ss format, with values in the range of ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’. |
|---|
| TIMESTAMP(fsp) | It stores values relative to the Unix Epoch, basically a Unix Timestamp. Values lie in the range of ‘1970-01-01 00:00:01’ UTC to ‘2038-01-09 03:14:07’ UTC. |
|---|
| YEAR | Stores values of years as a 4digit number format, with a range lying between -1901 to 2155. |
|---|
|
