Understanding SQL Data Types

Structured Query Language (SQL) supports various data types that define the kind of data a column can store in a database table. Choosing the right data type ensures efficient storage, faster queries, and data integrity. This guide covers the most common SQL data types across major database systems like MySQL, PostgreSQL, SQL Server, and Oracle.

1. Numeric Data Types

Used for storing numbers, including integers, decimals, and floating-point values.

Integer Types

• INT / INTEGER – Stores whole numbers (e.g., -2,147,483,648 to 2,147,483,647).
• SMALLINT – Smaller range than INT (-32,768 to 32,767).
• BIGINT – Larger range (-9 quintillion to +9 quintillion).
• TINYINT (MySQL) – Very small integers (0 to 255 or -128 to 127).

Decimal & Floating-Point Types

• DECIMAL(p,s) / NUMERIC(p,s) – Fixed-point numbers with precision and scale, e.g., DECIMAL(10,2).
• FLOAT – Approximate floating-point numbers.
• DOUBLE – Higher precision than FLOAT.

2. String (Text) Data Types

Used for storing text and character data.

Fixed-Length Strings

• CHAR(n) – Fixed-length string; pads with spaces if shorter.
• NCHAR(n) – Supports Unicode characters.

Variable-Length Strings

• VARCHAR(n) – Variable-length string (up to n characters).
• NVARCHAR(n) – Unicode-compatible version of VARCHAR.
• TEXT – Large blocks of text (MySQL/PostgreSQL).
• CLOB – Character Large Object for long text (Oracle).

3. Date & Time Data Types

Used for storing dates, times, and timestamps.

• DATE – Format: YYYY-MM-DD.
• TIME – Format: HH:MM:SS.
• DATETIME – Stores date and time together.
• TIMESTAMP – DATETIME with timezone support.
• YEAR (MySQL) – Stores a 4-digit year.

4. Binary Data Types

Used for storing binary data like images or files.

• BINARY(n) – Fixed-length binary data.
• VARBINARY(n) – Variable-length binary data.
• BLOB – Binary Large Object for storing files.
• LONGBLOB (MySQL) – For very large binary data (up to 4GB).

5. Boolean & Logical Data Types

Used for storing logical values like TRUE or FALSE.

• BOOLEAN (PostgreSQL) – TRUE or FALSE.
• BIT (SQL Server) – 0 or 1 values.
• TINYINT(1) (MySQL) – Used as boolean (0 = false, 1 = true).

6. Specialized Data Types

Used for storing structured or special data.

• JSON – Stores JSON format data (MySQL/PostgreSQL).
• XML – Stores XML data (SQL Server).
• UUID – Universally Unique Identifier (PostgreSQL).
• ENUM – Predefined string values (MySQL).

Choosing the Right Data Type

Tips for selecting optimal data types:

• Use INT for integers, and DECIMAL for precise values like currency.
• Choose VARCHAR over CHAR for dynamic-length strings.
• Use DATETIME for timestamps, and DATE for dates only.
• Opt for TEXT or BLOB for large data blocks.

Understanding SQL data types helps in designing efficient databases, improving performance, and ensuring integrity. Always refer to your specific SQL dialect documentation when choosing data types.