SQL Data Types
Learn via video course
FREE
DBMS Course - Master the Fundamentals and Advanced Concepts
by Srikanth Varma
1000
5
Overview
- When establishing a table, the SQL Data Type property determines the type of data that will be stored inside each column. The data type serves as a guideline for SQL to comprehend what type of data is expected within each column and identify how SQL will interact with the stored data.
- Integers, floating-point numbers, characters, strings, and arrays are examples of common data types. For more specific SQL uses, Dates, timestamps, boolean values, and varchar (variable character) types of formats.
What are Data Types in SQL?
A data type is a property that describes the sort of data that an object can store, such as integer data, character data, monetary data, date and time data, binary strings, and so on.
Let's look at a simple sign-up page for a website application. First Name, Last Name, and Contact Number are the three entry areas.
- The First/Last Name field will always be alphabetical.
- The Contact field will always be a phone number (i.e., numeric).
It is worth specifying "First/Last Name" as a character and "Contact" as an integer because a name can't be a number and a contact number can't be a character. It is obvious that all fields in any application include one or more types of data. For example, numeric, alphabetic, date, and many others.
Also, different data types have varied memory requirements. As a result, it makes it more logical to declare the column or variable with the data type that it will carry to make better use of memory.
Let us take another scenario to understand more about data types in SQL. Consider the following sample records from a sales table:
The following are the attributes of this table:
- customer id: an integer value that increases by one for each new customer.
- sales_id: an integer value that increases by one for each new sale.
- currency: always use the three-character currency code.
- purchase_time: the time of the sale.
- device: text, with values such as desktop, mobile app, and mobile web.
- has_discount: a boolean variable with entries that can be TRUE or FALSE.
The data type (integers, text, real numbers, etc...) and allowable value ranges (0 to 1,000; any 3 characters; etc...) match specific database data types.
Each column, local variable, expression, and parameter in SQL Server has a corresponding data type. SQL Server has a collection of system data types that specify all of the data types that can be utilized with SQL Server.
For any database, data types are primarily categorized into three categories:
- String Datatypes.
- Numeric Datatypes.
- Date and time Data types.
MySQL Data Types
MySQL String Data Types
| Data Type | Description | Size |
|---|---|---|
| CHAR(size) | A FIXED length string containing letters, numbers, and special characters. The size parameter determines the length of the column in characters. The default value is 1. | 0 - 255 bytes |
| VARCHAR(size) | A VARIABLE length string containing letters, numbers, and special characters. The size option sets the maximum character length for a column. | 0 - 65535 bytes |
| BINARY(size) | It is a type of function that stores binary byte strings. The size argument indicates the length of the column in bytes. The default value is 1. | 0 - 255 bytes |
| VARBINARY(size) | It is a type of function that stores binary byte strings. The size argument sets the maximum length of a column in bytes. | 0 - 65535 bytes |
| TINYBLOB | For use with BLOBs (Binary Large Objects). | 255 bytes |
| BLOG(size) | For use with BLOBs (Binary Large Objects). | 65,535 bytes |
| MEDIUMBLOB | For use with BLOBs (Binary Large Objects). | 16,777,215 bytes |
| LONGBLOG | For use with BLOBs (Binary Large Objects). | 4,294,967,295 bytes |
| TINYTEXT(size) | It stores a string. | 255 characters |
| TEXT(size) | It stores a string. | 65,535 characters |
| MEDIUMTEXT(size) | It stores a string. | 16,777,215 characters |
| LONGTEXT(size) | It stores a string. | 4,294,967,295 characters |
| ENUM(val1, val2, val3, ...) | It is used when a string object has only one value, which is selected from a list of possible values. If you enter a value that does not exist in the list, a blank value will be entered. | 65535 values |
| SET(val1, val2, val3, ...) | It is used to describe a string with 0 or more values from a list of available values. | 64 values |
MySQL Numeric Data Types
| Data Type | Description | Size |
|---|---|---|
| BOOL/ BOOLEAN | Synonym for TINYINT(1). Nonzero values are deemed true, while zero values are considered false. | |
| BIT(size) | A type of bit-value. Size specifies the number of bits per value. The size parameter accepts values ranging from 1 to 64. The default value is 1. | |
| TINYINT(size) | An extremely tiny integer. The signed range is -128 to 127. The unsigned range extends from 0 to 255. The size parameter determines the maximum width of the display (which is 255). | 1 byte |
| SMALLINT(size) | A small integer. The signed range is -32768 to 32767. The unsigned range is 0 to 65535. The size parameter determines the maximum width of the display (which is 255). | 2 bytes |
| MEDIUMINT(size) | A medium integer. The signed range is -8388608 to 8388607. The unsigned range is 0 to 16777215. The size parameter sets the maximum display width (which is 255). | 3 bytes |
| INT(size) | An integer value. The signed range is -2147483648 to -2147483647. The unsigned range is 0 to 4294967295. The size parameter determines the maximum width of the display (which is 255). | 4 bytes |
| INTEGER(size) | As same as to INT(size). | 4 bytes |
| BIGINT(size) | A large integer. The signed range is -9223372036854775808 to 9223372036854775807. Unsigned numbers range from 0 to 18446744073709551615. The size parameter determines the maximum width of the display (which is 255). | 8 bytes |
| FLOAT(size, d) | A number with a decimal value. Size specifies the total number of digits. The d parameter specifies the number of digits after the decimal point. It has been deprecated in MySQL 8.0.17 and will be removed in future releases. | 4 bytes |
| FLOAT( p) | A number with a decimal value. MySQL utilizes the p value to determine if the final data type is FLOAT or DOUBLE. If p is between 0 and 24, the data type is changed to FLOAT (). If p is between 25 and 53, the data type is changed to DOUBLE (). | 4 bytes if 0 <= p <= 24, 8 bytes if 25 <= p <= 53 |
| DOUBLE(size, d) | A standard-size floating-point number. Size specifies the total number of digits. The d option specifies the number of digits after the decimal point. | 8 bytes |
| DOUBLE PRECISION(size, d) | As same as DOUBLE(size, d) | 8 bytes |
| REAL(size, d) | As same as DOUBLE(size, d) | 8 bytes |
| DECIMAL(size, d) | A precise fixed-point number. Size specifies the total number of digits. The d option specifies the number of digits after the decimal point. The size has a maximum of 65 characters. D can have a maximum value of 30. Size is set to 10 by default. | Varies |
| DEC(size, d) | As same as DECIMAL(size, d) | Varies |
| NUMERIC(size, d) | As same as DECIMAL(size, d) | Varies |
| FIXED(size, d) | As same as DECIMAL(size, d) | Varies |
Note: All numeric data types may have an additional option: UNSIGNED or ZEROFILL. If the UNSIGNED option is specified, MySQL does not allow negative values for the column. When the ZEROFILL option is specified, MySQL adds the UNSIGNED property to the column by default.
MySQL Date and Time Data Types
| Data Type | Description | Format |
|---|---|---|
| DATE | It's a date. The permitted value range is '1000-01-01' to '9999-12-31'. | YYYY-MM-DD |
| DATETIME(fsp) | A combination of date and time. The range supported is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. By including DEFAULT and ON UPDATE in the column definition, automatic initialization and updating to the current date and time is achieved. | YYYY-MM-DD hh:mm |
| TIMESTAMP(fsp) | The number of seconds is represented in TIMESTAMP values. The acceptable time range is '1970-01-01 00:00:01 UTC' to '2038-01-09 03:14:07 UTC'. Automatic initialization and updating to the current date and time can be defined in the column definition by using DEFAULT CURRENT TIMESTAMP and ON UPDATE CURRENT TIMESTAMP. | YYYY-MM-DD hh:mm |
| TIME(fsp) | A time. The acceptable time range is '-838:59:59' to '838:59:59'. | hh:mm |
| YEAR | A year with four digits. The following four-digit values are permitted: 1901 to 2155, and 0000. Year in two-digit format is no longer supported by MySQL since version 8.0. |
SQL Server Data Types
SQL Server String Data Type
| Data Type | Description | Size |
|---|---|---|
| CHAR(size) | A character string data type with a fixed width. | 8000 characters |
| VARCHAR(size) | A character string data type with configurable width. | 8000 characters |
| VARCHAR(max) | A Character string data type with configurable width. | 1,073,741,824 characters |
| TEXT | A character string data type with configurable width. | 2 GB of text data |
| NCHAR(size) | A Unicode string data type with a fixed width. | 4000 characters |
| NVARCHAR(size) | A Unicode string data type with variable width. | 4000 characters |
| NVARCHAR(max) | A Unicode string data type with variable width. | 536,870,912 characters |
| NTEXT | A Unicode string data type with variable width. | 2 GB of text data |
| BINARY(size) | A binary string data type that is fixed. | 8000 bytes |
| VARBINARY(size) | A varying width Data type for binary strings. | 8000 bytes |
| VARBINARY(max) | A Binary string data type with configurable width. | 1,073,741,824 characters |
| IMAGE | A Binary string data type with configurable width. | 2 GB |
SQL Server Numeric Data Types
| Data Type | Description | Size |
|---|---|---|
| BIT | It is an integer that can have a value of 0 (zero), 1 (one), or null. | |
| TINYBIT | A set of whole numbers ranging from 0 to 255. | 1 byte |
| SMALLINT | A set of whole numbers ranging from -32,768 to 32,767. | 2 bytes |
| INT | A set of whole numbers ranging from -2,147,483,648 to 2,147,483,647. | 4 bytes |
| BIGINT | A set of whole numbers ranging from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. | 8 bytes |
| FLOAT(n) | Data with floating precision ranging from -1.79E+308 to 1.79E+308. The n option specifies whether the field should carry four or eight bytes. n has a default value of 53. | 4 or 8 bytes |
| REAL | Data with a floating precision ranging from -3.40E+38 to 3.40E+38. | 4 bytes |
| SMALLMONEY | Specifies monetary data ranging from -214,748.3648 to 214,748.3647. | 4 bytes |
| MONEY | Specifies monetary data ranging from -922,337,233,685,477.5808 to 922,337,203,685,477.5807. | 8 bytes |
| DECIMAL(p, s) | A set of Numbers with fixed precision and scale p specifies the maximum number of decimal digits that can be saved. s specifies the number of decimal digits stored to the right of the decimal point. When using the highest precision, acceptable numbers range from - 1038 +1 to 103 | 5-17 bytes |
| DEC(p, s) | As same as DECIMAL(p, s). | 5-17 bytes |
| NUMERIC(p,s) | As same as DECIMAL(p, s). | 5-17 bytes |
SQL Server Date and Time Data Type
| Data Type | Description | Format |
|---|---|---|
| DATETIME | A combination of date and time. With an accuracy of 3.33 milliseconds, it ranges from January 1, 1753 to December 31, 9999. | YYYY-MM-DD hh:mm |
| DATETIME2 | A combination of date and time. With an accuracy of 100 nanoseconds, it supports a range of January 1, 0001 to December 31, 9999. | YYYY-MM-DD hh:mm |
| DATE | Denotes a date. It is compatible with dates ranging from January 1, 0001 through December 31, 9999. | YYYY-MM-DD |
| TIME | Denotes a time. Time was only stored to an accuracy of 100 nanoseconds. The range varies from '00:00:00.0000000' to '23:59:59.9999999'. | YYYY-MM-DD hh:mm |
| DATETIMEOFFSET | A combination of date and time that incorporates time zone awareness based on UTC. With a precision of 100 nanoseconds, it supports a range of January 1, 0001 to December 31, 9999. | YYYY-MM-DD hh:mm |
| SMALLDATETIME | A combination of time and date. It has a range of January 1, 1900 to June 6, 2079 with a precision of 1 minute. | YYYY-MM-DD hh:mm |
| TIMESTAMP | When a new row is added or changed, it stores a unique number. The timestamp value is based on an internal clock and does not correlate to real-time. Each table can only have one timestamp variable. |
Oracle Data Types
Oracle String Data Types
| Data Type | Description | Size |
|---|---|---|
| CHAR(size) | It's used to keep character data inside a specific length limit | 2000 bytes |
| NCHAR(size) | It's used to keep national character data within a specific length limit | 2000 bytes |
| VARCHAR2(size) | It's used to keep variable string data inside a specific length limit. | 4000 bytes |
| VARCHAR(size) | It is equivalent to VARCHAR2 (size). VARCHAR(size) can also be used, although VARCHAR2 (size) is recommended. | 4000 bytes |
| NVARCHAR2(size) | It is used to hold Unicode string data that is less than a certain length. We must define the size of the NVARCHAR2 data type, | 4000 bytes |
Oracle Numeric Data Types
| Data Type | Description | Range |
|---|---|---|
| NUMBER(p, s) | It is a data type that includes Precision p and scale s. | 1<=p<=38 -84<=s<=127 |
| FLOAT ( p ) | It is a data type that is a subtype of the NUMBER data type. | 1<=p<=126 |
| BINARY_FLOAT | It is used to achieve binary precision ( 32-bit). It takes 5 bytes, including the length byte. | |
| BINARY_DOUBLE | It is used to achieve double binary precision (64-bit). It takes 9 bytes, including the length byte. |
Oracle Date and Time Data Types
| Data Type | Description | Format |
|---|---|---|
| DATETIME | It is used to store a fixed-length valid date-time format. Its time-span fluctuates from January 1, 4712 BC, and December 31, 9999 AD. | |
| TIMESTAMP | It is used to hold the valid date and time. | YYYY-MM-DD hh:mm |
Oracle Large Object Data Types (LOB Types)
| Data Type | Description | Size |
|---|---|---|
| BLOB | It is used to specify binary data that is unstructured. | 4,294,967,295 bytes |
| BFILE | It is used to save binary data in a separate external file. | 4,294,967,295 bytes |
| CLOB | It is used for character data that is only a single byte long. | 4,294,967,295 bytes |
| NCLOB | It specifies single-byte or fixed-length multibyte national character set (NCHAR) data. | 4,294,967,295 bytes |
| RAW(size) | It is used to specify raw binary data with variable length. It must have a maximum size defined. | 2000 bytes |
| LONGRAW | It is used to specify raw binary data with variable length. | 2 GB |
MS Access Data Types
Microsoft Access provides various data types that can be used to define fields in a table. The following are commonly used data types in MS Access:
- Text: Used for storing alphanumeric characters, such as names, addresses, and other text-based data.
- Number: Used for storing numerical values, such as integers, decimals, and fractions.
- Date/Time: Used for storing dates and times in a specific format.
- Currency: Used for storing monetary values with two decimal places.
- Yes/No: Used for storing Boolean values, which can be either True or False.
- Memo: Used for storing large amounts of text data, such as notes or comments.
- Attachment: Used for storing files, such as documents, images, or multimedia files, directly in a table.
- Hyperlink: Used for storing web addresses or links to files or other data sources.
- Lookup Wizard: Used for creating a list of values that users can choose from.
- OLE Object: Used for storing objects created in other applications, such as Microsoft Word or Excel, directly in a table.
Conclusion
- In an SQL server, a Data Type is defined as the type of data that any column or variable can store. It is a type of data that an object stores, such as an integer, character, string, and so on.
- The type of data that a column or variable can store must be defined in advance. In addition, determining data type prevents the user from inputting any unexpected or erroneous data.
- You can make better use of memory by assigning an appropriate data type to a variable or column, which will allocate only the amount of system memory required for the data in that column. I hope this tutorial has improved your understanding of data types in SQL.