Data Types in SQL

Introduction

When creating databases, one of the most fundamental concepts you need to understand is data types. In SQL, every column in a table must have a specified data type that determines what kind of data can be stored in that column. Choosing the right data type is crucial for several reasons:

• Data integrity: Ensures your data meets certain criteria
• Storage optimization: Different data types use different amounts of storage space
• Performance: Properly chosen data types can significantly improve query performance
• Functionality: Certain operations are only available for specific data types

In this tutorial, we'll explore the most common SQL data types, their uses, and best practices for choosing the right type for your data.

SQL Data Type Categories

SQL data types can be organized into the following categories:

1. Numeric Types: For storing numbers
2. Character String Types: For storing text
3. Date and Time Types: For storing dates and times
4. Binary Types: For storing binary data
5. Boolean Type: For storing true/false values
6. Special Types: JSON, XML, and more

Let's dive deeper into each category.

Numeric Data Types

Numeric data types are used to store numbers. SQL offers various numeric types with different ranges and precision.

Integer Types

Integer types store whole numbers without decimals.

Data Type	Storage	Range	Usage
TINYINT	1 byte	0 to 255 (unsigned)	Small integers like age
SMALLINT	2 bytes	-32,768 to 32,767	Medium-range integers
INT	4 bytes	-2.1B to 2.1B	Standard integer type
BIGINT	8 bytes	-9.2E18 to 9.2E18	Very large integers

Here's how to use integer types in a table:

sql

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  category_id SMALLINT,
  in_stock TINYINT,
  total_sold BIGINT
);

Decimal Types

For numbers that require decimal precision:

Data Type	Description	Usage
DECIMAL(p,s)	Exact numeric with precision p and scale s	Financial calculations
NUMERIC(p,s)	Same as DECIMAL	Financial calculations
FLOAT	Approximate numeric with floating precision	Scientific calculations
REAL	Approximate numeric with single precision	Scientific calculations
DOUBLE PRECISION	Approximate numeric with double precision	Complex calculations

Example of using decimal types:

sql

CREATE TABLE financial_transactions (
  transaction_id INT PRIMARY KEY,
  amount DECIMAL(10,2),  -- Can store up to 10 digits with 2 after decimal
  interest_rate FLOAT,
  calculation_factor DOUBLE PRECISION
);

caution

When working with monetary values, always use DECIMAL rather than FLOAT or DOUBLE to avoid rounding errors that can occur with floating-point types.

Character String Data Types

String data types store textual data:

Data Type	Description	Usage
CHAR(n)	Fixed-length string (1-255 chars)	Fixed-length data like state codes
VARCHAR(n)	Variable-length string (1-65,535 chars)	Most text data
TEXT	Variable unlimited length	Long text content

Example of using string types:

sql

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  state_code CHAR(2),
  bio TEXT
);

When choosing between CHAR and VARCHAR:

• Use CHAR when column length is fixed (like country codes)
• Use VARCHAR when length varies
• VARCHAR is more space-efficient for variable-length data

Date and Time Data Types

Date and time types store temporal data:

Data Type	Format	Range	Usage
DATE	YYYY-MM-DD	1000-01-01 to 9999-12-31	Birth dates, event dates
TIME	HH:MM:SS	-838:59:59 to 838:59:59	Time of day
DATETIME	YYYY-MM-DD HH:MM:SS	1000-01-01 to 9999-12-31	Time stamps
TIMESTAMP	YYYY-MM-DD HH:MM:SS	1970-01-01 to 2038-01-19	Auto-updating time stamps

Example usage:

sql

CREATE TABLE events (
  event_id INT PRIMARY KEY,
  event_name VARCHAR(100),
  event_date DATE,
  start_time TIME,
  created_at DATETIME,
  last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Working with Date/Time Types

Querying records based on dates:

sql

-- Find all events for next month
SELECT * FROM events 
WHERE event_date BETWEEN '2023-11-01' AND '2023-11-30';

-- Find events created in the last 24 hours
SELECT * FROM events 
WHERE created_at > NOW() - INTERVAL 1 DAY;

Binary Data Types

Binary types store binary data like files:

Data Type	Description	Max Size	Usage
BINARY(n)	Fixed-length binary data	255 bytes	Fixed-length binary data
VARBINARY(n)	Variable-length binary data	65,535 bytes	Variable-length binary data
BLOB	Binary Large Object	4GB	Files, images, documents

Example:

sql

CREATE TABLE documents (
  document_id INT PRIMARY KEY,
  document_name VARCHAR(100),
  file_data BLOB,
  file_hash BINARY(32)  -- For a SHA-256 hash (32 bytes)
);

tip

While SQL can store binary data, for large files it's often better to store files in the filesystem and store just the file path in the database.

Boolean Data Type

Some SQL databases support a native BOOLEAN type, which can store TRUE or FALSE:

sql

CREATE TABLE user_preferences (
  user_id INT PRIMARY KEY,
  receive_emails BOOLEAN,
  dark_mode BOOLEAN,
  auto_login BOOLEAN
);

In databases without a native BOOLEAN type (like MySQL), TINYINT(1) is often used instead, where 1 represents TRUE and 0 represents FALSE.

Special Data Types

Many SQL databases support specialized data types for particular use cases:

JSON Data Type

For storing JSON documents (available in MySQL 5.7+, PostgreSQL, SQL Server):

sql

CREATE TABLE settings (
  user_id INT PRIMARY KEY,
  preferences JSON
);

-- Insert JSON data
INSERT INTO settings (user_id, preferences) 
VALUES (1, '{"theme": "dark", "fontSize": 14, "notifications": true}');

-- Query JSON data (PostgreSQL syntax)
SELECT user_id, preferences->'theme' AS theme
FROM settings;

Enumerated Type

ENUM allows you to define a list of possible values for a column (available in MySQL):

sql

CREATE TABLE tickets (
  ticket_id INT PRIMARY KEY,
  status ENUM('open', 'in_progress', 'resolved', 'closed') DEFAULT 'open'
);

-- Only these values can be inserted
INSERT INTO tickets (ticket_id, status) VALUES (1, 'open');
INSERT INTO tickets (ticket_id, status) VALUES (2, 'in_progress');

Data Type Considerations

When choosing data types, consider:

1. Storage Requirements

Each data type requires a different amount of storage. Using the smallest appropriate data type can significantly reduce your database size, especially for tables with millions of rows.

2. Performance Impact

• Smaller data types are faster to process
• Indexes on smaller data types are more efficient
• Joining tables on columns with the same type is faster

3. Database Compatibility

Data types can vary between different database systems. For example:

• MySQL uses VARCHAR for variable-length strings
• PostgreSQL uses TEXT with no length limitation
• SQL Server uses VARCHAR(MAX) for large strings

If your application might need to work with different database systems, consider data type compatibility.

Real-World Examples

Let's look at some practical examples of data type usage in real-world scenarios.

E-commerce Database Schema

sql

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  description TEXT,
  price DECIMAL(10,2) NOT NULL,
  stock_quantity INT NOT NULL DEFAULT 0,
  category_id SMALLINT,
  is_available BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT NOT NULL,
  order_date DATETIME NOT NULL,
  total_amount DECIMAL(12,2) NOT NULL,
  status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
  shipping_address TEXT NOT NULL,
  payment_details JSON
);

User Profile System

sql

CREATE TABLE users (
  user_id INT PRIMARY KEY,
  username VARCHAR(50) UNIQUE NOT NULL,
  email VARCHAR(100) UNIQUE NOT NULL,
  password_hash BINARY(60) NOT NULL,  -- For bcrypt hashes
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  birth_date DATE,
  registration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  last_login DATETIME,
  account_status ENUM('active', 'suspended', 'inactive') DEFAULT 'active',
  preferences JSON
);

Best Practices

1. Use the smallest data type that can reliably contain all possible values
2. Be consistent with data types across tables, especially for columns you plan to join
3. Use DECIMAL for money, not floating-point types
4. Consider adding constraints (like NOT NULL, DEFAULT, etc.) along with data types
5. Document your schema with comments explaining any non-obvious type choices

sql

CREATE TABLE financial_records (
  record_id INT PRIMARY KEY,
  amount DECIMAL(10,2) NOT NULL COMMENT 'Always in USD, 2 decimal places',
  transaction_date DATE NOT NULL,
  description VARCHAR(200) DEFAULT 'Unlabeled transaction'
);

Summary

SQL data types are fundamental building blocks of any database schema. Choosing the right data type for each column ensures:

• Data integrity and validation
• Optimal storage usage
• Better query performance
• Appropriate functionality

Remember that different SQL database systems may have variations in supported data types and their exact behaviors. Always consult the documentation for your specific database system when designing your schema.

Exercises

1. Create a table to store information about books, including the title, author, publication date, page count, price, and whether it's available as an e-book.

2. Design a schema for a blog platform with tables for users, posts, comments, and categories. Choose appropriate data types for each column.

3. Write a query to convert a VARCHAR column containing dates in the format 'MM/DD/YYYY' to a proper DATE data type.

Additional Resources

• Your database's official documentation for complete data type specifications
• Database design books focusing on optimization
• Online SQL tutorial websites with interactive exercises

Now that you understand SQL data types, you're ready to design efficient and effective database schemas for your applications!