Home / SQL / Data Types

Data Types

Overview

SQL data types define the type of data that can be stored in a column. Choosing the appropriate data type is crucial for data integrity, storage efficiency, and performance.

Data types vary slightly between database systems, but the core concepts remain consistent across most SQL databases.

Common SQL Data Types

String Data Types

Data Type	Description	Example
`CHAR(n)`	Fixed-length string (pads with spaces)	`CHAR(10)`
`VARCHAR(n)`	Variable-length string (up to n characters)	`VARCHAR(255)`
`TEXT`	Large text data	`TEXT`

Numeric Data Types

Data Type	Description	Example
`INT`	Integer (32-bit)	`INT`
`BIGINT`	Large integer (64-bit)	`BIGINT`
`DECIMAL(p,s)`	Fixed-point number (p=precision, s=scale)	`DECIMAL(10,2)`
`FLOAT`	Floating-point number	`FLOAT`
`DOUBLE`	Double-precision floating-point	`DOUBLE`

Date and Time Data Types

Data Type	Description	Example
`DATE`	Date only (YYYY-MM-DD)	`DATE`
`TIME`	Time only (HH:MM:SS)	`TIME`
`DATETIME`	Date and time	`DATETIME`
`TIMESTAMP`	Automatic timestamp (updates on change)	`TIMESTAMP`

Boolean Data Type

Data Type	Description	Example
`BOOLEAN`	True/false value	`BOOLEAN` or `BIT`

Examples

Example 1: Table with Various Data Types

Using different data types

CREATE TABLE Products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(200) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    stock_quantity INT DEFAULT 0,
    is_available BOOLEAN DEFAULT TRUE,
    created_date DATE DEFAULT CURRENT_DATE,
    last_updated TIMESTAMP
);

Choosing the Right Data Type

Considerations when selecting data types:

Storage efficiency: Use smallest appropriate type
Data range: Ensure type can hold all possible values
Precision: Use DECIMAL for financial data, not FLOAT
Performance: Some types are faster for operations
Future needs: Consider growth requirements

String Types: CHAR vs VARCHAR

Understanding when to use each:

CHAR vs VARCHAR

CHAR(10):
- Always uses 10 characters of storage
- Pads with spaces if value is shorter
- Use for fixed-length data (e.g., codes)

VARCHAR(255):
- Uses only storage needed (up to 255)
- No padding
- Use for variable-length data (e.g., names)

Numeric Types: DECIMAL vs FLOAT

Important differences:

DECIMAL vs FLOAT

DECIMAL(10,2):
- Exact precision (important for money)
- Fixed-point arithmetic
- Use for: Prices, amounts, financial data

FLOAT:
- Approximate precision
- Floating-point arithmetic
- Use for: Scientific calculations, measurements

Database-Specific Variations

Different databases have varying data types:

MySQL: AUTO_INCREMENT, ENUM, SET
PostgreSQL: SERIAL, JSON, JSONB, ARRAY
SQL Server: MONEY, SMALLMONEY, UNIQUEIDENTIFIER
Oracle: NUMBER, CLOB, BLOB

Best Practices

Use appropriate types: Match data requirements
Avoid over-sizing: Don't use VARCHAR(1000) for names
Use DECIMAL for money: Never FLOAT for financial data
Consider internationalization: Use UTF8/UTF8MB4 for text
Document choices: Note why specific types were chosen

Common Mistakes

Using FLOAT for money: Causes rounding errors
Over-sizing VARCHAR: Wastes storage and can hurt performance
Wrong date type: Using VARCHAR for dates
Not considering NULL: Remember NULL is allowed unless NOT NULL

Next Steps

Now that you understand data types, review how to use them in CREATE TABLE, or explore Constraints for enforcing data rules.

← Views