Table of content:

SQL Data Types Explained with Examples for Beginners

Structure Query Language (SQL) data types are essential for defining the kind of data that can be stored in a table column. Each data type ensures that the data conforms to a specific format, which helps maintain data integrity, improve performance, and optimize storage. SQL offers a variety of data types, categorized into numeric, string, date & time, boolean, binary, and miscellaneous types.

Choosing the right type improves storage efficiency, query performance, and data integrity, ensuring that the database functions efficiently for its intended use.

Numeric Data Types in SQL

Numeric data types are used for storing numbers, whether whole numbers or decimal values. SQL supports a range of number formats, suitable for tasks from counting items to handling precise financial calculations.

Integer Types

Data Type	Description	Example
TINYINT	Very small integers (usually 0 to 255)	100
SMALLINT	Small integers	32767
MEDIUMINT	Medium-range integers (MySQL specific)	8388607
INT or INTEGER	Standard integer	2147483647
BIGINT	Large integers	9223372036854775807

Decimal & Floating-Point Types

Data Type	Description	Example
DECIMAL(p, s)	Fixed-point number with 'p' digits, 's' after decimal. High precision.	DECIMAL(5,2) → 123.45
NUMERIC(p, s)	Synonym for DECIMAL. Used interchangeably.	NUMERIC(8,3) → 12345.678
FLOAT(p)	Approximate floating-point. Can lose precision.	FLOAT(6) → 3.14159
REAL	Single-precision floating-point	2.71828
DOUBLE PRECISION / DOUBLE	Double-precision floating-point	1.2345678901

Character/String Data Types in SQL

The character/string data types are used to store alphanumeric values such as names, emails, or any other textual content. SQL provides various text data types to accommodate both fixed and variable-length strings, as well as longer text like descriptions or articles.

Data Type	Description	Example
CHAR(n)	Fixed-length string. Always uses 'n' characters (padded with spaces).	'ABC ' (CHAR(6))
VARCHAR(n)	Variable-length string. Uses space only as needed (up to n chars).	'OpenAI'
TEXT	Long text. Can store large strings (length varies by DBMS).	Articles, blogs
TINYTEXT	Max 255 characters (MySQL).	'Short bio'
MEDIUMTEXT	Medium-sized text (~16MB, MySQL).	Blog posts
LONGTEXT	Very long text (~4GB, MySQL).	Books, documents
NCHAR(n)	Fixed-length Unicode string. Useful for multilingual text.	Unicode text
NVARCHAR(n)	Variable-length Unicode string	Multilingual text

For more information on CHAR and VARCHAR data types in the SQL server, click here.

Date and Time Data Types in SQL

Date and time data types are essential for storing information like birthdays, timestamps, or event logs. These help in managing and querying data chronologically in formats like date-only, time-only, or both.

Data Type	Description	Example
DATE	Stores date only	'2025-04-08'
TIME	Stores time only	'14:30:00'
DATETIME	Stores date and time	'2025-04-08 14:30:00'
TIMESTAMP	Like DATETIME, but it auto-updates based on the current system time.	'2025-04-08 14:30:00'
YEAR	Stores year (usually 2 or 4 digits)	'2025'

Boolean Data Type in SQL

The Boolean data type is used for storing truth values—typically representing yes/no or true/false conditions. It's often used in decision-making columns like “is_active” or “is_verified”. Stores true/false values.

Data Type	Description	Example
BOOLEAN	Stores TRUE or FALSE. Often implemented as 1 (true) or 0 (false).	TRUE / FALSE

Binary Data Types in SQL

The Binary data types are designed to store binary data such as images, multimedia files, or encrypted content. They’re commonly used when working with non-textual data in applications like file storage or document databases. Store binary data, such as files, images, etc.

Data Type	Description	Use Case
BINARY(n)	Fixed-length binary data	Hashes, file signatures
VARBINARY(n)	Variable-length binary data	Encrypted data
BLOB	Binary Large Object (up to several GBs)	Images, audio, video
TINYBLOB	Small BLOB (max 255 bytes)	Small images or icons
MEDIUMBLOB	Medium BLOB (~16 MB)	Medium files
LONGBLOB	Large BLOB (~4 GB)	High-res images/videos

Miscellaneous Data Types (DBMS-Specific) in SQL

Besides standard SQL types, many DBMSs offer special data types like JSON, XML, spatial data, and UUIDs for unique needs. Though not part of core SQL, they boost functionality and performance in specific use cases and may vary across systems.

Data Type	Description	DBMS Support
ENUM	Set of predefined values	MySQL
SET	Multiple predefined values in a single column	MySQL
XML	XML-formatted data	SQL Server, PostgreSQL
JSON	JSON-formatted data	MySQL, PostgreSQL
UUID	Universally Unique Identifier	PostgreSQL, MySQL
GEOMETRY	Spatial data (GIS)	MySQL
ARRAY	Array of values	PostgreSQL

Conclusion

In conclusion, SQL data types are essential for ensuring efficient data storage, accuracy, and optimal performance in database management. Choosing the appropriate data type for each column can improve query speed, save storage space, and maintain data integrity. Whether it's numeric, string, date, or binary data, understanding and selecting the right data type is a fundamental step in database design.

Mastering SQL data types is key, but SQL keywords are just as essential. Click the link for a detailed guide on SQL Keywords.

Time for a Short Quiz

  QUIZZ SNIPPET IS HERE

  QUIZZ SNIPPET IS HERE

  QUIZZ SNIPPET IS HERE

Frequently Asked Questions (FAQs)

1. What is the difference between CHAR and VARCHAR data types?

CHAR(n) stores fixed-length strings and always uses exactly n bytes, padding with spaces if necessary.
VARCHAR(n) stores variable-length strings up to n characters, using only the space needed.
Use CHAR for data with fixed size (e.g., country codes) and VARCHAR for variable-length data (e.g., names).

2. When should I use DECIMAL instead of FLOAT or DOUBLE?

Use DECIMAL (or NUMERIC) when precision is crucial, such as in financial calculations (e.g., currency).
FLOAT and DOUBLE are approximate and may lead to rounding errors.

3. What is the difference between DATE, DATETIME, and TIMESTAMP?

DATE: Stores only the date (e.g., 2025-04-08)
DATETIME: Stores date and time (e.g., 2025-04-08 14:30:00)
TIMESTAMP: Similar to DATETIME, but often used to track record changes and time zone-aware in some DBMS.
Use TIMESTAMP for auto-tracking changes and DATETIME for regular date-time values.

4. What is a BLOB, and when should I use it?

BLOB (Binary Large Object) stores binary data, such as images, audio, videos, or files.
It's not human-readable and is used when you need to store multimedia or file content inside the database.
Only use BLOBs if storing binary in DB is necessary; otherwise, store file paths and keep files in a storage service.

5. Are BOOLEAN and TINYINT(1) the same in MySQL?

In MySQL, BOOLEAN is a synonym for TINYINT(1), where:

- 0 = FALSE
- 1 = TRUE