sql_datatypes

SQL Data Types Overview

Choosing the correct data type for each column in your tables is crucial for data integrity, storage efficiency, and performance. While SQL defines standard data types, specific implementations and available types can vary between database management systems (DBMS). This guide provides an overview of common data types in popular systems like PostgreSQL, MySQL, and SQL Server.

General Categories of SQL Data Types

Most SQL data types fall into these broad categories:

Character Strings: For text data (e.g., names, descriptions). Fixed-length (CHAR) or variable-length (VARCHAR, TEXT).
Numeric Types: For numbers. Exact numerics (e.g., INTEGER, DECIMAL) or approximate numerics (e.g., FLOAT, REAL).
Date and Time Types: For storing dates, times, timestamps, and intervals.
Boolean Type: For storing true/false values.
Binary Types: For storing binary data (e.g., images, files).
Special Types: Including JSON, XML, UUID, arrays, spatial data types, etc., depending on the DBMS.

PostgreSQL Data Types (Common Examples)

PostgreSQL offers a rich set of data types conforming closely to SQL standards.

Character Types:

Data Type        | Description
-----------------|----------------------------------------------------------------------
`VARCHAR(n)`     | Variable-length string with a limit of n characters.
`CHAR(n)`        | Fixed-length string, blank-padded to n characters.
`TEXT`           | Variable-length string of (effectively) unlimited length.

Numeric Types:

Data Type        | Description
-----------------|----------------------------------------------------------------------
`SMALLINT`       | 2-byte signed integer.
`INTEGER` or `INT`| 4-byte signed integer.
`BIGINT`         | 8-byte signed integer.
`DECIMAL(p,s)` or `NUMERIC(p,s)` | Exact numeric type with user-defined precision (p) and scale (s).
`REAL`           | 4-byte single-precision floating-point number.
`DOUBLE PRECISION`| 8-byte double-precision floating-point number.
`SERIAL`         | Auto-incrementing 4-byte integer (for primary keys).
`BIGSERIAL`      | Auto-incrementing 8-byte integer (for primary keys).

Date/Time Types:

Data Type        | Description
-----------------|----------------------------------------------------------------------
`DATE`           | Stores date (year, month, day).
`TIME [WITHOUT TIME ZONE]` | Stores time of day.
`TIME WITH TIME ZONE` | Stores time of day, with time zone.
`TIMESTAMP [WITHOUT TIME ZONE]` | Stores date and time.
`TIMESTAMP WITH TIME ZONE` | Stores date and time, with time zone (converted to UTC for storage).

Boolean Type:

Data Type        | Description
-----------------|----------------------------------------------------------------------
`BOOLEAN`        | Stores `TRUE`, `FALSE`, or `NULL`.

Other Common Types:

Data Type        | Description
-----------------|----------------------------------------------------------------------
`UUID`           | Universally Unique Identifier.
`JSON`, `JSONB`  | For storing JSON data (`JSONB` is generally preferred for indexing and functions).
`BYTEA`          | Variable-length binary string.

MySQL Data Types (Common Examples)

MySQL provides a range of data types, with some specific behaviors.

Text Types:

Data Type        | Description (Max length/bytes depends on character set)
-----------------|----------------------------------------------------------------------
`CHAR(size)`     | Fixed-length string, 0 to 255 characters.
`VARCHAR(size)`  | Variable-length string, 0 to 65,535 bytes total for row.
`TINYTEXT`       | Max 255 bytes.
`TEXT`           | Max 65,535 bytes.
`MEDIUMTEXT`     | Max 16,777,215 bytes.
`LONGTEXT`       | Max 4,294,967,295 bytes.
`ENUM(...)`      | A string object that can have only one value, chosen from a list.
`SET(...)`       | A string object that can have zero or more values, chosen from a list.

Numeric Types:

Data Type        | Description (UNSIGNED option available for non-negative numbers)
-----------------|----------------------------------------------------------------------
`TINYINT`        | 1-byte integer.
`SMALLINT`       | 2-byte integer.
`MEDIUMINT`      | 3-byte integer.
`INT` or `INTEGER`| 4-byte integer.
`BIGINT`         | 8-byte integer.
`FLOAT(p,s)`     | Single-precision floating-point. Precision optional.
`DOUBLE(p,s)`    | Double-precision floating-point. Precision optional.
`DECIMAL(p,s)` or `NUMERIC(p,s)` | Fixed-point number. `p` is total digits, `s` is digits after decimal.

Date and Time Types:

Data Type        | Description
-----------------|----------------------------------------------------------------------
`DATE`           | 'YYYY-MM-DD' format. Range '1000-01-01' to '9999-12-31'.
`DATETIME(fsp)`  | 'YYYY-MM-DD HH:MM:SS[.fraction]' format. Optional fractional seconds precision (fsp).
`TIMESTAMP(fsp)` | 'YYYY-MM-DD HH:MM:SS[.fraction]' format. Range '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC. Supports automatic initialization/update to current time.
`TIME(fsp)`      | 'HH:MM:SS[.fraction]' format.
`YEAR`           | Year in 4-digit format (YYYY). Range 1901 to 2155.

Other Common Types:

Data Type        | Description
-----------------|----------------------------------------------------------------------
`BLOB` family    | For binary large objects (`TINYBLOB`, `BLOB`, `MEDIUMBLOB`, `LONGBLOB`).
`JSON`           | Native JSON data type for storing and manipulating JSON documents.

SQL Server Data Types (Common Examples)

SQL Server offers a comprehensive list of data types.

String Types:

Data Type        | Description (Storage depends on actual length)
-----------------|----------------------------------------------------------------------
`CHAR(n)`        | Fixed-length non-Unicode characters. Max 8,000 characters.
`VARCHAR(n)`     | Variable-length non-Unicode characters. Max 8,000 characters.
`VARCHAR(MAX)`   | Variable-length non-Unicode characters. Max 2^31-1 bytes (2 GB).
`NCHAR(n)`       | Fixed-length Unicode characters. Max 4,000 characters.
`NVARCHAR(n)`    | Variable-length Unicode characters. Max 4,000 characters.
`NVARCHAR(MAX)`  | Variable-length Unicode characters. Max 2^30-1 characters (2 GB).
Deprecated: `TEXT`, `NTEXT` (use `VARCHAR(MAX)`, `NVARCHAR(MAX)` instead).

Numeric Types:

Data Type        | Description
-----------------|----------------------------------------------------------------------
`BIT`            | Integer data with a value of 0, 1, or NULL.
`TINYINT`        | Integer data from 0 through 255.
`SMALLINT`       | Integer data from -32,768 through 32,767.
`INT`            | Integer data from -2,147,483,648 through 2,147,483,647.
`BIGINT`         | Integer data from -2^63 through 2^63-1.
`DECIMAL(p,s)` or `NUMERIC(p,s)` | Fixed precision (p) and scale (s) numbers.
`SMALLMONEY`     | Monetary data from -214,748.3648 to 214,748.3647.
`MONEY`          | Monetary data from -922,337,203,685,477.5808 to 922,337,203,685,477.5807.
`FLOAT(n)`       | Approximate-number data values. `n` specifies precision (1-53).
`REAL`           | Approximate-number data values (equivalent to `FLOAT(24)`).

Date and Time Types:

Data Type        | Description
-----------------|----------------------------------------------------------------------
`DATE`           | Stores a date (YYYY-MM-DD). Range 0001-01-01 through 9999-12-31.
`TIME(n)`        | Stores a time (hh:mm:ss.fractional_seconds). Precision `n` from 0 to 7.
`DATETIME2(n)`   | Stores date and time with fractional seconds. Precision `n` from 0 to 7. Preferred over `DATETIME`.
`DATETIMEOFFSET(n)` | Date and time with time zone awareness.
`SMALLDATETIME`  | Date and time, accuracy to the minute. Range 1900-01-01 through 2079-06-06.
`DATETIME`       | Older date and time type. Range 1753-01-01 through 9999-12-31.

Other Data Types:

Data Type        | Description
-----------------|----------------------------------------------------------------------
`BINARY(n)`      | Fixed-length binary data.
`VARBINARY(n)`   | Variable-length binary data.
`VARBINARY(MAX)` | Variable-length binary data. Max 2 GB. Replaces `IMAGE`.
`UNIQUEIDENTIFIER`| Stores a globally unique identifier (GUID).
`XML`            | Stores XML data. Max 2 GB.
`ROWVERSION` (formerly `TIMESTAMP`) | Database-generated unique binary number. Used for version-stamping rows. **Not a date/time value.**

Microsoft Access Data Types (Briefly):

While less common for new, large-scale development, MS Access types include Short Text (formerly Text, up to 255 chars), Long Text (formerly Memo), Number (Byte, Integer, Long Integer, Single, Double, Decimal), Date/Time, Currency, AutoNumber, Yes/No (Boolean), OLE Object, Hyperlink. Modern best practice suggests migrating to more robust server-based RDBMS for larger applications.

Choosing the Right Data Type:

Nature of Data: Select a type that accurately represents the data (e.g., don’t use text for numbers if you need calculations).
Range of Values: Ensure the type can accommodate all possible values for the column.
Precision: For numeric types, choose appropriate precision and scale.
Storage Space: Use the smallest data type that can reliably store the data to save space and improve performance.
Performance: Correct data types can impact query performance, especially for joins and indexing.
Future Needs: Consider potential future requirements but avoid over-specifying.

Always refer to the official documentation for your specific database system version for the most accurate and detailed information on data types.