SQL Data Types

When creating tables in an RDBMS, every column must be assigned a data type. A data type defines what kind of data can be stored in that column (numbers, text, dates, binary data) and how the database engine allocates physical storage and performs calculations.

Choosing the right data type ensures data integrity (preventing invalid entries like text in an age column) and query performance (reducing disk space and memory overhead).

Data Type Categories

Although specific names vary across database engines, standard SQL data types fall into four primary categories:

                          SQL DATA TYPES
                                |
     +-----------------+--------+--------+-----------------+
     |                 |                 |                 |
  String / Text     Numeric         Date & Time         Binary / JSON
  (VARCHAR, CHAR)   (INT, DECIMAL)  (DATE, TIMESTAMP)   (BLOB, JSONB)

1. String & Character Data Types

Used to store letters, numbers, symbols, and unstructured textual data.

Standard SQL Data Type	Purpose & Description	Common Usage
`VARCHAR(n)`	Variable-length string. Stores up to n characters. Uses only the space required by the actual text.	Names, emails, addresses, general text.
`CHAR(n)`	Fixed-length string. Pads shorter entries with trailing spaces to match length n.	Fixed-length codes (e.g., country codes like `'US'`, ISO codes).
`TEXT` / `VARCHAR(MAX)`	Variable-length string for holding extremely large bodies of text (up to gigabytes).	Product reviews, article bodies, JSON strings.

-- Examples
CountryCode CHAR(2)       -- Always reserves exactly 2 characters (e.g., 'US', 'IN')
Email       VARCHAR(255)  -- Holds up to 255 chars, stores only active string length
Bio         TEXT          -- Holds large unstructured text

2. Numeric Data Types

Split into Exact Numerics (integers, fixed-point decimals) and Approximate Numerics (floating-point numbers).

Exact Integers

Data Type	Storage Size	Value Range	Typical Usage
`TINYINT`	1 Byte	-128 to 127 (or 0 to 255)	Age, status flags, low-range counters.
`SMALLINT`	2 Bytes	-32,768 to 32,767	Years, quantity values.
`INT` / `INTEGER`	4 Bytes	-2.14 Billion to 2.14 Billion	Standard Primary Keys, user IDs.
`BIGINT`	8 Bytes	≈ -9 × 10¹⁸ to 9 × 10¹⁸	High-volume transaction keys, global log IDs.

Fixed-Point & Floating-Point

Data Type	Description	Best Used For
`DECIMAL(p, s)` / `NUMERIC(p, s)`	Exact fractional values. p = total precision (total digits), s = scale (digits after decimal point).	Financial & Currency Data (e.g., `DECIMAL(10, 2)` holds up to `99999999.99` without rounding errors).
`FLOAT` / `DOUBLE`	Approximate floating-point numbers. Faster calculations, but subject to rounding precision issues.	Scientific measurements, sensor readings, data metrics where exact micro-precision is secondary.

3. Date & Time Data Types

Used to record temporal attributes. Handling dates correctly prevents time-zone issues and enables built-in date math.

Data Type	Format	Example	Description
`DATE`	`YYYY-MM-DD`	`2026-09-27`	Stores calendar dates without time.
`TIME`	`HH:MM:SS`	`14:30:00`	Stores time of day without dates.
`DATETIME` / `TIMESTAMP`	`YYYY-MM-DD HH:MM:SS`	`2026-09-27 14:30:00`	Stores combined date and time of day.
`TIMESTAMP WITH TIME ZONE`	Full timestamp + UTC offset	`2026-09-27 14:30:00+05:30`	Stores timestamp localized to UTC offsets (PostgreSQL standard).

4. Binary & Specialized Data Types

Modern database engines offer native types for unstructured or semi-structured data:

BOOLEAN: Stores logical values (TRUE, FALSE, or NULL). Note: SQL Server uses BIT (1 or 0) instead.
JSON / JSONB: Stores structured JSON documents. Native to PostgreSQL, MySQL, and modern SQL engines for semi-structured document models.
BLOB / VARBINARY: Binary Large Objects used for storing raw files, images, PDF documents, or encrypted hashes directly on disk.

Cross-Engine Data Type Comparison

Data types can vary slightly depending on your RDBMS engine:

Standard Type	PostgreSQL	MySQL	SQL Server
Variable String	`VARCHAR(n)` / `TEXT`	`VARCHAR(n)` / `TEXT`	`VARCHAR(n)` / `NVARCHAR(n)`
Standard Integer	`INTEGER`	`INT`	`INT`
Boolean Flag	`BOOLEAN`	`BOOLEAN` (Alias for `TINYINT(1)`)	`BIT`
Auto Increment Key	`SERIAL` / `GENERATED ALWAYS`	`AUTO_INCREMENT`	`IDENTITY(1,1)`
JSON Document	`JSONB`	`JSON`	`NVARCHAR(MAX)` (With JSON functions)