SQL Data Types – Foundations for Data Integrity

Understanding SQL data types is crucial for building robust, scalable, and high-performance databases. Data types define the kind of data a column can hold, enforcing consistency, saving storage space, and improving query performance.

In this chapter, we’ll cover SQL data types in great detail — from basic types like integers and strings to advanced types like BLOBs and JSON. We’ll also explore use cases, best practices, and system-specific differences.

Why Are Data Types Important?

Assigning the correct data type to a column:

• Validates input (e.g., Age should be an integer, not text)
• Optimizes storage (smaller types save space)
• Improves performance (enables better indexing and caching)
• Enforces integrity (e.g., DATE columns only accept valid dates)

Example:

CREATE TABLE Employees (
  ID INT PRIMARY KEY,
  Name VARCHAR(100),
  JoiningDate DATE,
  Salary DECIMAL(10,2)
);

Each column has a type that ensures only appropriate data is stored.

SQL Standard Data Type Categories

SQL data types fall into the following major categories:

1. Numeric
2. Character/String
3. Date and Time
4. Boolean
5. Binary (BLOB)
6. Special/Complex Types (e.g., JSON, ENUM)

We will now explore each of these categories in depth.

1. Numeric Data Types

Numeric types store numbers — both whole and decimal. They are used for counting, storing IDs, calculating totals, and more.

Integer Types

Type	Description	Size (bytes)
TINYINT	Small integers (e.g., age < 256)	1
SMALLINT	Medium small integers	2
INT/INTEGER	Standard whole numbers	4
BIGINT	Very large whole numbers	8

Use case: Use INT for IDs, TINYINT for ratings (0–5), BIGINT for large counts like total views.

CREATE TABLE Products (
  ProductID INT PRIMARY KEY,
  StockCount SMALLINT
);

Decimal Types

Used for precise values such as currency or scientific measurements.

Type	Description
DECIMAL(p,s)	Fixed-point (precision & scale)
NUMERIC(p,s)	Synonym for DECIMAL
FLOAT	Approximate, floating-point value
REAL	Less precise float
DOUBLE	Double-precision floating-point

Example:

CREATE TABLE Salaries (
  EmployeeID INT,
  Salary DECIMAL(10,2) -- e.g., 99999999.99
);

Tip: For money, use DECIMAL, not FLOAT, to avoid rounding errors.

2. Character (String) Data Types

Used to store names, descriptions, addresses, and other textual data.

CHAR vs VARCHAR

Type	Description
CHAR(n)	Fixed-length string, right-padded with spaces
VARCHAR(n)	Variable-length string
TEXT	Large blocks of text (e.g., articles)

Example:

CREATE TABLE Users (
  Username VARCHAR(50),
  Password CHAR(64) -- fixed-length hash
);

Best practices:

• Use CHAR for fixed values (e.g., country codes)
• Use VARCHAR for names, emails, etc.
• Avoid TEXT unless needed — can’t be indexed efficiently

3. Date and Time Data Types

Used to track timestamps, appointments, transactions, etc.

Type	Description
DATE	Only date (YYYY-MM-DD)
TIME	Only time (HH:MM:SS)
DATETIME	Combined date and time
TIMESTAMP	Stores UTC timestamp; can auto-update
YEAR	Only year (2 or 4 digits)

Example:

CREATE TABLE Orders (
  OrderID INT,
  OrderDate DATE,
  ShippedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Best practices:

• Use DATE when time isn’t important
• Use TIMESTAMP for audit trails and logs
• Store in UTC; convert to local time in applications

4. Boolean Data Type

Represents truth values — TRUE or FALSE.

In SQL:

• Some systems use BOOLEAN
• Others use TINYINT(1) (1 = true, 0 = false)

CREATE TABLE Settings (
  SettingID INT,
  IsEnabled BOOLEAN DEFAULT TRUE
);

Note: MySQL uses BOOLEAN as an alias for TINYINT(1).

5. Binary (BLOB) Data Types

Used for storing files like images, PDFs, or binary data.

Type	Description
BINARY(n)	Fixed-length binary data
VARBINARY(n)	Variable-length binary data
BLOB	Binary Large Object

CREATE TABLE Documents (
  DocID INT,
  FileData BLOB
);

Use case: Avoid storing large files in databases unless necessary. Use BLOBs only if file security or portability is critical.

6. Special and Advanced Data Types

ENUM

Stores predefined values. Useful for categories.

CREATE TABLE Products (
  Category ENUM('Electronics', 'Clothing', 'Books')
);

SET

Allows multiple values from a predefined list.

CREATE TABLE Features (
  FeatureSet SET('Bluetooth', 'WiFi', 'GPS')
);

JSON

Stores JSON documents (MySQL, PostgreSQL support native JSON).

CREATE TABLE Logs (
  EventID INT,
  Meta JSON
);

Best practices:

• Use ENUM/SET only for controlled vocabulary
• Use JSON sparingly — great for semi-structured data, but can complicate querying

Choosing the Right Data Type – Key Considerations

1. Data Nature – Is it numeric, textual, date-based?
2. Storage Size – Choose the smallest type that fits your data
3. Precision Needs – Use DECIMAL for money, not FLOAT
4. Performance – Avoid large TEXT/BLOB unless essential
5. Querying Needs – Avoid types that can’t be indexed efficiently (e.g., TEXT, JSON in some engines)

System-Specific Variations

Different RDBMSs support slightly different type definitions:

MySQL

• TINYINT, TEXT, ENUM, SET, JSON
• No true BOOLEAN (alias for TINYINT)

PostgreSQL

• Strong support for BOOLEAN, ARRAY, JSONB, UUID, INET
• Flexible with custom types

SQL Server

• NVARCHAR for Unicode
• BIT for Boolean
• MONEY, SMALLMONEY for currency

Oracle

• NUMBER, VARCHAR2, CLOB, BLOB
• Strong DATE/TIMESTAMP support

Always refer to the documentation of the target system.

Validating and Constraining Data

Pairing data types with constraints ensures clean data:

CREATE TABLE Employees (
  ID INT PRIMARY KEY,
  Name VARCHAR(100) NOT NULL,
  Salary DECIMAL(10,2) CHECK (Salary >= 0),
  Active BOOLEAN DEFAULT TRUE
);

This approach prevents errors and enforces business logic at the schema level.

Common Mistakes and How to Avoid Them

1. Using FLOAT for Money – Use DECIMAL instead to avoid precision errors
2. Overusing VARCHAR(255) – Reserve space wisely
3. Storing Dates as Strings – Use DATE/TIMESTAMP
4. Improper Default Values – Avoid hardcoding time-sensitive defaults
5. Ignoring Nullability – Decide where NULL makes sense

Summary

Data types are the building blocks of relational databases. Choosing the right type:

• Enforces data quality
• Improves performance
• Reduces storage

From simple integers to complex JSON documents, SQL provides a wide array of types to model real-world data accurately.

In the next chapter, we’ll explore the SQL command categories — DDL, DML, DCL, and TCL — and how each command serves a specific purpose in database management and interaction.