Data Types in SQL

Data types define the kind of data that can be stored in a column, variable, or expression. Choosing the correct data type is critical for data integrity, performance, and storage efficiency.

---

Why Data Types Matter

• Storage: Different data types consume different amounts of storage.
• Validation: They enforce what values can be stored in a column.
• Performance: Using the right type improves query speed and indexing.
• Portability: Being aware of standard vs vendor-specific types helps with migration.

---

Common SQL Data Types

Below are the most common categories of data types in SQL. The exact names and limits can vary slightly between RDBMSs.

---

1. Numeric Data Types

Used for storing numbers.

Type	Description	Example
INT	Integer numbers (whole numbers)	1, -100
SMALLINT	Smaller-range integer	32000
BIGINT	Larger-range integer	9223372036854775807
DECIMAL(p,s) or NUMERIC(p,s)	Fixed precision and scale	12.34
FLOAT / REAL	Approximate numeric with floating point	3.14159
BIT	Boolean or bit field	0, 1

---

2. Character and String Data Types

Used for storing text data.

Type	Description	Example
CHAR(n)	Fixed-length string	'ABC'
VARCHAR(n)	Variable-length string	'Hello World'
TEXT	Large variable-length text	Long articles

---

3. Date and Time Data Types

Used for storing dates and times.

Type	Description	Example
DATE	Date only (year, month, day)	'2025-06-26'
TIME	Time only (hour, minute, second)	'14:30:00'
DATETIME	Date and time	'2025-06-26 14:30:00'
TIMESTAMP	Date and time, usually includes time zone info	'2025-06-26 14:30:00+00'
INTERVAL	Time span or duration	'1 day', '5 hours' (PostgreSQL)

---

4. Binary Data Types

Used for storing binary data such as files, images, or blobs.

Type	Description	Example
BINARY	Fixed-length binary data	Binary blobs
VARBINARY	Variable-length binary data	Files
BLOB	Binary Large Object	Images, documents

---

5. Miscellaneous Data Types

Some databases provide additional types for specialized use cases.

Type	Description	Example
BOOLEAN	True or false values	TRUE, FALSE
JSON	Stores JSON data	'{"key": "value"}'
UUID	Universally Unique Identifier	'550e8400-e29b-41d4-a716-446655440000'
ARRAY	Stores arrays of elements	'{1,2,3}'
XML	Stores XML data	<note>Hello</note>

---

Vendor-Specific Variations

Keep in mind:

• MySQL: Uses TINYINT for booleans (0/1).
• PostgreSQL: Has robust JSON and ARRAY support.
• SQL Server: Uses UNIQUEIDENTIFIER for UUIDs and NTEXT/NVARCHAR for Unicode.

Always check the exact data type implementations for your RDBMS.

---

Best Practices for Choosing Data Types

1. Use the smallest type that fits your data — it saves storage and speeds up processing.
2. Be explicit with precision — for monetary values, always use DECIMAL or NUMERIC rather than FLOAT to avoid rounding errors.
3. Normalize text — use Unicode (NVARCHAR) for multi-language support.
4. Be consistent — keep types consistent across tables for keys and joins.
5. Consider indexing — some types, like large text or blobs, aren’t index-friendly.

---

Summary

Choosing the right data types is a small decision with a big impact. They affect data accuracy, query performance, storage, and compatibility. Mastering data types is fundamental to writing robust, efficient SQL.

Tip: Always check your database’s documentation for exact type limits and behaviors.