SQL - Data Types

SQL Data Types: The Foundation of Your Database

What Are SQL Data Types?

A SQL data type is a rule that specifies the kind of data a column can hold in a database table. Think of it as a container designed for a specific purpose: one for numbers, one for text, one for dates, and so on. By defining a data type for each column, you tell the database what to expect, ensuring data integrity and optimizing storage.

When creating a table, every column must be assigned two key attributes:

• A column name (e.g., user_email)
• A data type (e.g., VARCHAR(255))

Key Idea: Columns define the structure and rules for your data, while rows contain the actual data records that follow those rules.

Why Choosing the Right Data Type Matters

Selecting the appropriate data type is a critical design decision with significant impact:

• Data Integrity: It prevents incorrect data from being stored. You can’t put text like ‘hello’ into an INTEGER column.
• Performance: The database can process queries faster when it works with correctly sized and typed data. Searching a column of integers is much faster than searching a text column.
• Storage Efficiency: Allocating the right amount of space prevents wasted memory and disk space. Storing a person’s age (0-150) in a TINYINT is far more efficient than using a BIGINT.

Core Data Type Categories

While specific names can vary slightly between database systems (like PostgreSQL, MySQL, SQL Server), most support the same fundamental categories of data types.

1. Numeric Types

Used for storing numerical data, from whole numbers to decimals.

Common Type	Description	Typical Use Case
INTEGER (or INT)	Whole numbers (no decimals). Comes in various sizes like TINYINT, SMALLINT, INTEGER, BIGINT to store numbers of different ranges.	User ID, quantity, age.
DECIMAL(p, s) (or NUMERIC)	Fixed-point numbers with exact precision. p is the total number of digits, and s is the number of digits after the decimal point.	Financial data, currency, scientific measurements requiring precision.
FLOAT, REAL, DOUBLE PRECISION	Floating-point numbers for approximate values. Suitable for scientific calculations where absolute precision is not the primary concern.	Scientific calculations, large statistical data sets.

2. String (Character) Types

Used for storing text data.

Common Type	Description	Typical Use Case
CHAR(n)	Fixed-length string. If the stored string is shorter than n, it’s padded with spaces. Use only when all values are the same length.	Two-letter state codes (e.g., ‘CA’, ‘TX’), status flags (‘Y’/‘N’).
VARCHAR(n)	Variable-length string with a maximum length of n characters. This is the most common string type.	Usernames, email addresses, article titles, any text with varying length.
TEXT	Variable-length string for very long text. The modern equivalent in many databases is VARCHAR(MAX) (SQL Server) or simply using TEXT (PostgreSQL, MySQL).	Blog posts, product descriptions, comments.

Pro-Tip: When dealing with international text, ensure your database’s character set is set to UTF8MB4 to correctly store a wide range of characters and emojis.

3. Date and Time Types

Used for storing temporal information.

Common Type	Description	Typical Use Case
DATE	Stores only the date (year, month, day).	Birthdate, publication date.
TIME	Stores only the time of day (hour, minute, second).	Store opening hours, event start time.
TIMESTAMP (or DATETIME)	Stores both date and time. TIMESTAMP WITH TIME ZONE is highly recommended for applications serving users in different regions, as it stores the timezone information.	Post creation time (created_at), event logs, appointment times.

4. Boolean Types

Common Type	Description	Typical Use Case
BOOLEAN (or BIT)	Stores TRUE or FALSE values. Some databases use a BIT(1) or TINYINT(1) to simulate this, storing 1 for true and 0 for false.	Flags like is_active, has_subscribed, email_verified.

5. Specialized Types

Modern databases support advanced data types for specific use cases.

Common Type	Description	Typical Use Case
JSON / JSONB	Stores semi-structured JSON data directly in the database. JSONB is a binary format that is often faster for queries.	Storing user settings, product attributes, API responses.
UUID	Stores a Universally Unique Identifier, a 128-bit number used to uniquely identify information in computer systems.	Primary keys in distributed systems to avoid ID collisions.
ENUM	A value chosen from a predefined list of allowed values. For example, ENUM('pending', 'processing', 'shipped').	Order status, user roles, priority levels.

Defining Data Types in a Table

You define data types when you create a table using the CREATE TABLE statement. The modern convention is to use lowercase for SQL keywords.

create table users (
    id         UUID primary key default gen_random_uuid(), -- Modern, robust primary key
    username   VARCHAR(50) not null unique,
    email      VARCHAR(255) not null unique,
    is_active  BOOLEAN not null default true,
    created_at TIMESTAMP with time zone not null default now(),
    settings   JSONB
);

In this modern example:

• id is a UUID, ensuring it’s unique across systems.
• username and email are VARCHAR with NOT NULL and UNIQUE constraints for data integrity.
• is_active is a BOOLEAN with a sensible default value.
• created_at uses a timezone-aware timestamp, which is crucial for global applications.
• settings uses JSONB to store flexible, schemaless data.

Dialect-Specific Data Types (A Quick Note)

While the core concepts are universal, implementation details differ. For example:

• SQL Server: Uses NVARCHAR for Unicode strings, DATETIME2 for higher precision dates, and MONEY for currency.
• MySQL: TIMESTAMP behavior can vary with versions. ENUM is a popular native type.
• Oracle: Uses VARCHAR2 as its standard variable string and NUMBER(p,s) for all numeric types.
• MS Access: A desktop database with types like Short Text and Long Text. It is not typically used for modern web or application development.

Always consult the official documentation for your specific database version to see the full list of available data types and their syntax.

Best Practices and Common Pitfalls

• Be Specific: Don’t use VARCHAR(255) for everything. If a value, like a country code, will always be 2 characters, use CHAR(2) or VARCHAR(2).
• Store Numbers as Numbers: Never store numbers you intend to perform calculations on (like price or quantity) as strings. This kills performance and leads to errors.
• Use Timezone-Aware Timestamps: For any application with users in different timezones, TIMESTAMP WITH TIME ZONE is a necessity, not a luxury.
• Avoid Deprecated Types: In SQL Server, avoid TEXT, NTEXT, and IMAGE. Use VARCHAR(MAX), NVARCHAR(MAX), and VARBINARY(MAX) instead.
• Pitfall: The FLOAT Trap: Avoid using FLOAT or REAL for financial data. Their approximate nature can lead to rounding errors. Use DECIMAL or NUMERIC for currency.