Data types in SQL

In SQL, data types define the kind of values a column, variable, or parameter can hold. Different database systems (MySQL, PostgreSQL, SQL Server, Oracle, etc.) have slightly different sets, but they all fall into similar categories.

Here’s a structured breakdown of common SQL data types:

1. Numeric Data Types

Used to store numbers.

• Integer types
  • INT / INTEGER → whole numbers (range varies by DB).
  • SMALLINT → smaller range of whole numbers.
  • BIGINT → very large integers.
  • TINYINT (MySQL, SQL Server) → very small integers.
• Decimal / exact numeric
  • DECIMAL(p, s) or NUMERIC(p, s) → fixed-point number with precision p (total digits) and scale s (digits after decimal).
• Floating-point
  • FLOAT → approximate numeric values with floating decimal.
  • REAL → single precision floating point.
  • DOUBLE PRECISION / DOUBLE → higher precision floating point.

2. String Data Types

Used to store text.

• Fixed-length
  • CHAR(n) → fixed-length string, padded with spaces if shorter.
• Variable-length
  • VARCHAR(n) → variable-length string up to n characters.
  • TEXT / CLOB → very large text (database-dependent).
• Special (some DBs)
  • NCHAR, NVARCHAR → Unicode strings.
  • LONGTEXT, MEDIUMTEXT (MySQL).

3. Date & Time Data Types

Used to store temporal values.

• DATE → stores year, month, and day.
• TIME → stores hour, minute, second.
• DATETIME → stores both date and time.
• TIMESTAMP → date and time with time zone (varies by DB).
• YEAR (MySQL) → year only.
• INTERVAL (PostgreSQL, Oracle) → time duration.

4. Boolean Data Type

• BOOLEAN / BOOL → stores TRUE or FALSE (in some DBs, it’s stored as TINYINT(1)).

5. Binary Data Types

Used for raw binary data (like images, files).

• BINARY(n) → fixed-length binary data.
• VARBINARY(n) → variable-length binary data.
• BLOB (Binary Large Object) → large binary storage.

6. Other / Advanced Types

(depending on database system)

• JSON → stores JSON data (MySQL, PostgreSQL).
• XML → stores XML data (SQL Server, Oracle).
• UUID → stores universally unique identifiers.
• ENUM → stores a predefined set of values (MySQL).
• ARRAY → stores arrays of values (PostgreSQL).
• GEOMETRY / POINT → spatial data types.