A column’s data type is essentially the type of data format that will be used to store the data in each cell; examples include any type of integer, character, money, date and time, binary, etc.
An SQL data type refers to the type of data that can be stored in a column of a database table. In a column, the user can store numeric, string, binary, etc by defining data types. For example integer data, character data, monetary data, date and time data, binary strings, and so on.
While creating a database table in a database, we need to specify the following two attributes to define a table column:
- Name of the column
- The data type of the column
For example, if you want to store a student name in a column then you should give the column name something like student_name and its data type will be char(50) which means it can store a string of characters up to 50 characters.
The data type provides guidelines for SQL to understand what type of data is expected inside each column, and hence, prevents the user from entering any unexpected or invalid data in a column. For example, if we want a column to store only integer values, we can specify its data type as INT. SQL will show an error if any other value apart from an integer is inserted into that particular column.
Defining a Data Type
During the creation of a table in a database, developers define SQL data types. They must specify the respective data type and size along with the name of the column when creating a table.
Following is the syntax to specify a data type in MySQL −
CREATE TABLE table_name(column1 datatype, column2 datatype....)Let us look at an example query below to understand better
CREATE TABLE Customers (Name VARCHAR (25), Age INT);In the above SQL query, we are creating a table of Customers. And since the Name column only stores string values, we are specifying its data type as “VARCHAR”. The VARCHAR data type represents string values in SQL. Similarly, we define the Age column with the integer data type, “INT”.
Types of SQL Data Types
There are three main types of SQL data types available in any RDBMS. They are listed below −
- String Data types
- Numeric Data types
- Date and Time Data types
MySQL String Data Types
| CHAR(Size) | It is used to specify a variable-length string that can contain numbers, letters, and special characters. Its size can be from 0 to 65535 characters. |
| VARCHAR(Size) | It is equal to CHAR() but stores binary byte strings. Its size parameter specifies the column length in the bytes. The default is 1. |
| BINARY(Size) | It is equal to CHAR() but stores binary byte strings. Its size parameter specifies the column length in the bytes. The default is 1. |
| VARBINARY(Size) | It is equal to VARCHAR() but stores binary byte strings. Its size parameter specifies the maximum column length in bytes. |
| TEXT(Size) | It holds a string that can contain a maximum length of 255 characters. |
| TINYTEXT | It holds a string with a maximum length of 255 characters. |
| MEDIUMTEXT | It holds a string with a maximum length of 16,777,215. |
| LONGTEXT | It is used when a string object having only one value, is chosen from a list of possible values. It contains 65535 values in an ENUM list. If you insert a value that is not in the list, a blank value will be inserted. |
| ENUM(val1, val2, val3,…) | It is used when a string object having only one value, chosen from a list of possible values. It contains 65535 values in an ENUM list. If you insert a value that is not in the list, a blank value will be inserted. |
| SET( val1,val2,val3,….) | It is used to specify a string that can have 0 or more values, chosen from a list of possible values. You can list up to 64 values at one time in a SET list. |
| BLOB(size) | It is used for BLOBs (Binary Large Objects). It can hold up to 65,535 bytes. |
MySQL Numeric Data Types
| BIT(Size) | It is used for a bit-value type. The number of bits per value is specified in size. Its size can be 1 to 64. The default value is 1. | |
| INT(size) | It is used to specify a floating point number. Its size parameter specifies the total number of digits. The number of digits after the decimal point is specified by the d parameter. | |
| INTEGER(size) | It is equal to INT(size). | |
| FLOAT(size, d) | It is a normal size floating point number. Its size parameter specifies the total number of digits. The number of digits after the decimal is specified by the d parameter. | |
| FLOAT(p) | It is used to specify a fixed point number. Its size parameter specifies the total number of digits. The number of digits after the decimal parameter is specified by the d parameter. The maximum value for the size is 65, and the default value is 10. The maximum value for d is 30, and the default value is 0. | |
| DOUBLE(size, d) | It is used to specify a floating point number. MySQL used the p parameter to determine whether to use FLOAT or DOUBLE. If p is between 0 and 24, the data type becomes FLOAT (). If p is from 25 to 53, the data type becomes DOUBLE(). | |
| DECIMAL(size, d) | It is used to specify a fixed point number. Its size parameter specifies the total number of digits. The number of digits after the decimal parameter is specified by d parameter. The maximum value for the size is 65, and the default value is 10. The maximum value for d is 30, and the default value is 0. | |
| DEC(size, d) | It is equal to DECIMAL(size, d). | |
| BOOL | It is used to specify Boolean values true and false. Zero is considered as false, and nonzero values are considered as true. |
MySQL Date and Time Data Types
| DATE | It is used to specify a year in a four-digit format. Values allowed in four-digit format from 1901 to 2155, and 0000. |
| DATETIME(fsp) | It is used to specify date and time combinations. Its format is YYYY-MM-DD hh:mm:ss. Its supported range is from ‘1000-01-01 00:00:00′ to 9999-12-31 23:59:59’. |
| TIMESTAMP(fsp) | It is used to specify a year in a four-digit format. Values allowed in four digit format from 1901 to 2155, and 0000. |
| TIME(fsp) | It is used to specify the time format. Its format is hh:mm:ss. Its supported range is from ‘-838:59:59’ to ‘838:59:59’ |
| YEAR | It is used to specify the time format. Its format is hh:mm: ss. Its supported range is from ‘-838:59:59’ to ‘838:59:59’ |
SQL Server Data Types
SQL Server String Data Type
| char(n) | It is a variable-width character string data type. Its size can be up to 8000 characters. |
| varchar(n) | It is a variable width character string data type. Its size can be up to 8000 characters. |
| varchar(max) | It is a variable-width character string data type. Its size can be up to 2GB of text data. |
| text | It is a variable width character string data type. Its size can be up to 2GB of text data. |
| nchar | It is a fixed width Unicode string data type. Its size can be up to 4000 characters. |
| nvarchar | It is a variable width Unicode string data type. Its size can be up to 4000 characters. |
| ntext | It is a variable width Unicode string data type. Its size can be up to 2GB of text data. |
| binary(n) | It is a fixed width Binary string data type. Its size can be up to 8000 bytes. |
| varbinary | It is a variable width Binary string data type. Its size can be up to 8000 bytes. |
| image | It is also a variable width Binary string data type. Its size can be up to 2GB. |
SQL Server Numeric Data Types
| bit | It is an integer that can be 0, 1 or null. |
| tinyint | It allows whole numbers from 0 to 255. |
| Smallint | It allows whole numbers between -32,768 and 32,767. |
| Int | It allows whole numbers between -2,147,483,648 and 2,147,483,647. |
| bigint | It allows whole numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807. |
| float(n) | It is used to specify floating precision number data from -1.79E+308 to 1.79E+308. The n parameter indicates whether the field should hold the 4 or 8 bytes. Default value of n is 53. |
| real | It is a floating precision number data from -3.40E+38 to 3.40E+38. |
| money | It is used to specify monetary data from -922,337,233,685,477.5808 to 922,337,203,685,477.5807. |
SQL Server Date and Time Data Type
| datetime | It is used to specify date and time combination. It supports range from January 1, 1753, to December 31, 9999 with an accuracy of 3.33 milliseconds. |
| datetime2 | It is used to specify date and time combination. It supports range from January 1, 0001 to December 31, 9999 with an accuracy of 100 nanoseconds |
| date | It is used to store date only. It supports range from January 1, 0001 to December 31, 9999 |
| time | It stores time only to an accuracy of 100 nanoseconds |
| timestamp | It stores a unique number when a new row gets created or modified. The time stamp value is based upon an internal clock and does not correspond to real time. Each table may contain only one-time stamp variable. |
Conclusion
In conclusion, SQL data types play a crucial role in defining the structure and characteristics of data stored in relational databases. By choosing appropriate data types, developers ensure data integrity, optimize storage space and enhance query performance. Understanding the various data types available in SQL, such as numeric, character, date/time, and binary types, empowers database designers to make informed decisions when designing schemas and selecting the most suitable data types for each column. Additionally, modern SQL dialects often provide extensions and additional data types to cater to specific needs, further expanding the versatility and flexibility of SQL databases. Overall, mastering SQL data types is essential for creating efficient, scalable, and robust database systems.
Frequently Asked Questions
SQL data types are attributes that specify the type of data that each column can hold in a database table. They define the nature of the data, such as integers, characters, dates, or binary data.
SQL data types are essential for maintaining data integrity, optimizing storage space, and improving query performance. By defining the type of data each column can store, data types help ensure consistency and accuracy in the database.
SQL broadly categorizes data types into several groups, including numeric, character, date/time, binary, and miscellaneous types. Each category encompasses specific data types tailored to diverse data storage requirements.
Developers commonly use numeric data types like INTEGER, SMALLINT, BIGINT, DECIMAL, and FLOAT to store numeric values, such as whole numbers, decimal numbers, or floating-point numbers.
SQL offers various character data types, including CHAR, VARCHAR, TEXT, and CLOB. Developers utilize these data types to store character strings of different lengths.
