Introduction to SQL Data Types
Data types in SQL, just like any other programming language, define what type of data can the column or the variable hold. The type of data can be anything from a character, through a series of characters, through numbers, to date and time. A data type is a very crucial concept of tightly-coupled programming. It helps resolve most of the type compatibility errors during program compilation itself.
We would be broadly categorizing SQL Data types and then exploring various types and examples under those categories.
Note – Not all database management systems support all the data types. For example, Oracle does not support DateTime. These specifics are given along with the description of the data type in the article. It is advised to be mindful of this while designing the database. Similarly, there are many other data types that are specific to any particular database management system. For example, MS SQL has a money data type. Such types are not generic and are beyond the scope of this article.
Types of SQL Data
1. Binary
Binary data type holds a sequence of binary byte strings. Unlike the usual character strings, the binary strings are used to store unconventional data such as images.
Binary(size): Stores binary byte strings of column length specified by the size parameter. The size of the column is fixed. If the size is defined as 10 bytes and data stored is 5 bytes, the remaining 5 bytes are still occupied in the memory by the column. These remaining bytes are null padded to the right by most SQL parsers. The maximum size possible is 8000 bytes.
Varbinary(size): Stores binary byte strings of column length specified by the size parameter. The size of the column is not fixed. If the size is defined as 10 bytes and data stored is 5 bytes, the column occupies only 5 bytes in the memory. The maximum size possible is 8000 bytes.
4.5 (4,249 ratings)
View Course
Varbinary(max): Specifying the keyword max in the size parameter increases the maximum possible size to 2GB and allocates variable size to columns based on the data stored. This data type is not supported by MySQL.
Example:
create table BinaryDataTypes (col_binary BINARY(5), col_varbinary VARBINARY(5));
insert into BinaryDataTypes values (101, 121);
select * from BinaryDataTypes;
insert into BinaryDataTypes values (12345, 123456);
insert into BinaryDataTypes values (123456, 123456);
The error occurs because we had defined the size of columns as 5 bytes only.
2. Character/String
These data types store either a single character or a series of characters forming a string. These include characters, numbers and special characters. The nature of the character(s) is however limited to non-Unicode characters only.
Char(size): Stores fixed-length character strings of the length specified by the size parameter. The sizing of the column works in a similar fashion to the BINARY data type. The default size is 1 character. The maximum is 255 in most database management systems.
Varchar(size): Stores variable-length character strings. However, the length specified by the size parameter defines the maximum length of the column. The maximum possible length is 65535 characters.
Varchar(max): Specifying the keyword max in the size parameter increases the maximum possible size to 2GB and allocates variable size to columns based on the data stored. This data type is not supported by MySQL.
Text: MySQL supports text data type instead of varchar(max). The text also increases the maximum possible size to 2GB.
Example:
create table CharacterDataTypes (col_char CHAR(300));
create table CharacterDataTypes (col_char CHAR(20), col_varchar VARCHAR(20), col_text TEXT);
insert into CharacterDataTypes values ('Hello World', 'How you doing?', 'This is an incredibly longer text to demonstrate the data type text in MySQL. The data type text makes it possible to store 2GB of data in a column. That\'s equal to 2*1024*1024*1024 = over 2.14 billion characters. Whoa!');
3. Unicode Character/String
These data types store either a single or a string of Unicode characters.
Nchar(size): This is similar to char data type and supports Unicode characters as well. The maximum limit is however reduced to 4000 bytes.
Nvarchar(size): This is similar to the nvarchar data type and supports Unicode characters as well. The maximum limit is however reduced to 4000 bytes.
Nvarchar(max): Specifying the keyword max in the size parameter increases the maximum possible size to 1GB and allocates variable size to columns based on the data stored. This data type is not supported by MySQL.
Ntext: MySQL supports the ntext data type instead of nvarchar(max). Ntext also increases the maximum possible size to 1GB.
Example:
create table UnicodeCharacterDataTypes (col_unicode_char NCHAR(200));
insert into UnicodeCharacterDataTypes values ('局层局层');
select * from UnicodeCharacterDataTypes;
4. Numeric
Numeric data types support integers and real/fractional/decimal numbers.
Bit/Bool/Boolean: These datatypes store only two values – 0 and 1. 0 denotes false while 1 denotes true. A bit is supported by most databases. Some advanced databases do support bool and boolean as well.
INT: Stores positive and negative integers up to 4 Bytes in size. This means the value must be in the range [-2147483648, 2147483647]. Specifying the keyword unsigned restricts the column to store only positive values in the range [0, 4294967295].
TINYINT: Stores positive and negative integers up to 1 Byte in size. This means the value must be in the range [-128, 127]. Specifying the keyword unsigned restricts the column to store only positive values in the range [0, 255].
Smallint: Stores positive and negative integers up to 2 Bytes in size. This means the value must be in the range [-32768, 32767]. Specifying the keyword unsigned restricts the column to store only positive values in the range [0, 65535].
Bigint: Stores positive and negative integers up to 8 Bytes in size. This means the value must be in the range [-263, 263-1]. Specifying the keyword unsigned restricts the column to store only positive values in the range [0, 264-1].
Decimal (size, D): Stores a fixed-point number. The parameter size specifies the total number of digits, whereas d specifies the number of digits following the decimal point. Default values for size and d are 10 and 0 respectively.
Float (size, D) / Double (size, D): Stores a floating-point number. Float is used for smaller numbers up to 4 Bytes in size. Double stores larger numbers. However, double is not supported by all databases.
Out-of-syllabus knowledge – The difference between fixed-point and floating-point numbers is that the decimal point is fixed in one and floating in another. Fixed-point means that the number of digits is fixed after and before the decimal point. Floating-point means that the number of digits before and after the decimal point can vary relative to the significance of the digits in the number.
Example:
create table NumericDataTypes (col_tinyint_u TINYINT UNSIGNED, col_smallint SMALLINT, col_int INT, col_bigint BIGINT UNSIGNED, col_decimal DECIMAL(5,2), col_float FLOAT);
insert into NumericDataTypes values (255, -32768, 1234567890, (POWER(2, 63)-1), 987.65, 987.65);
5. Date & Time
- Date: Stores the data in the format YYYY-MM-DD, limited from 1000-01-01 to 9999-12-31.
- Time: Stores the time in the format hh:mm: ss.
- DateTime: Stores both the date and time.
- TimeStamp: The timestamp is used to mark a timestamp whenever a row is entered or updated in the table. This is usually done through keywords DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP.
Example:
create table DateTimeDataTypes (col_date DATE, col_time TIME, col_datetime DATETIME, col_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
insert into DateTimeDataTypes(col_date, col_time, col_datetime) values (CURDATE(), '08:50:41', NOW());
6. Miscellaneous-SQL Data Types
- CLOB: CLOB stands for Character Large Objects. They can hold character data for up to 2GB. Not supported by MySQL though. MySQL uses Text to achieve the same.
- BLOB: BLOB stands for Binary Large Objects. They are used for storing binary objects of size up to 2GB. Usually, images are converted into binary objects and stored in BLOB columns.
- XML: Used for storing XML data.
- JSON: Used for storing JSON data. Not supported by MySQL.
Conclusion
The SQL data types help a lot in strategically optimizing the database space. In a digital world, where data is ever-growing at an exponential rate, a developer must wisely choose the type for every bit of data. This helps in the reduction of costs arising due to massive data storage.
Recommended Articles
This is a guide to SQL Data Types. Here we discuss the Introduction, Types of SQL Data, Character/String, Unicode Character/String, and Numeric in detail. You can also go through our other suggested articles to learn more–
