SQL Data Types

When working with databases in SQL (Structured Query Language), it’s important to store data in the correct format. This is where SQL Data Types come into play.

A data type defines the kind of values that can be stored in a column of a table. Choosing the right data type improves storage efficiency, query performance, and ensures data accuracy.

In this guide, we will explore SQL Data Types, their categories, and examples.

---

Why Data Types are Important in SQL?

1. Accuracy – Ensures that only the right kind of data is stored. For example, an INT column will only accept numbers.
2. Efficiency – Saves storage space by allocating only the required memory.
3. Validation – Helps prevent invalid data entry (e.g., storing text in a date column).
4. Performance – Queries run faster when correct data types are used.

---

Categories of SQL Data Types

SQL data types are broadly divided into the following categories:

1. Numeric Data Types – For storing numbers.
2. String (Character) Data Types – For storing text.
3. Date and Time Data Types – For storing dates, times, and timestamps.
4. Boolean Data Type – For storing true/false values.
5. Binary Data Types – For storing binary data like images or files.
6. Other Special Data Types – Depending on the database system (e.g., JSON, XML, ENUM).

---

1. Numeric Data Types

Numeric types are used to store numbers, either whole numbers or numbers with decimals.

Integer Types

• INT / INTEGER – Stores whole numbers.
  • Example: INT can store values from -2,147,483,648 to 2,147,483,647.
  • Example usage: CREATE TABLE Students ( StudentID INT, Age INT );
• SMALLINT – Stores smaller whole numbers.
  • Range: -32,768 to 32,767.
  • Saves memory compared to INT.
• BIGINT – For very large integers.
  • Range: up to ±9 quintillion.

Decimal Types

• DECIMAL(p, s) or NUMERIC(p, s) – Stores exact decimal numbers.
  • p = precision (total digits), s = scale (digits after decimal).
  • Example: DECIMAL(5, 2) → 999.99.
• FLOAT / REAL / DOUBLE PRECISION – Stores approximate decimal values.
  • Useful for scientific calculations where exact precision is not required.
  • Example: CREATE TABLE Products ( Price DECIMAL(8, 2), Discount FLOAT );

---

2. String (Character) Data Types

Used for storing text, names, descriptions, etc.

CHAR(n)

• Fixed-length string.
• Always stores exactly n characters, padding with spaces if necessary.
• Example: CHAR(5) – 'SQL ' (with spaces).

VARCHAR(n)

• Variable-length string.
• Stores only the characters entered, up to a maximum of n.
• Example: VARCHAR(50) – Stores names or email addresses efficiently.

TEXT

• For large text values (paragraphs, articles, descriptions).
• Can store up to several gigabytes depending on the database.

Example:

CREATE TABLE Employees (
    FirstName VARCHAR(30),
    LastName VARCHAR(30),
    Bio TEXT
);

---

3. Date and Time Data Types

Used for storing dates, times, and timestamps.

DATE

• Stores date values in YYYY-MM-DD format.
• Example: '2025-08-08'.

TIME

• Stores time values in HH:MM:SS format.
• Example: '14:30:00'.

DATETIME / TIMESTAMP

• Stores both date and time.
• Example: '2025-08-08 14:30:00'.
• Often used for recording when a record was created or updated.

YEAR

• Stores year in two-digit or four-digit format.
• Example: 2025.

Example:

CREATE TABLE Orders (
    OrderID INT,
    OrderDate DATE,
    OrderTime TIME,
    LastUpdated TIMESTAMP
);

---

4. Boolean Data Type

Stores only TRUE or FALSE values.

• In some databases, it is represented as BOOLEAN.
• In others (like MySQL), it is stored as TINYINT(1) where 1 = TRUE and 0 = FALSE.

Example:

CREATE TABLE Users (
    UserID INT,
    IsActive BOOLEAN
);

---

5. Binary Data Types

Used to store raw binary data such as images, videos, or files.

BINARY(n)

• Fixed-length binary data.

VARBINARY(n)

• Variable-length binary data.

BLOB (Binary Large Object)

• Stores large binary data such as multimedia files.

Example:

CREATE TABLE Documents (
    DocID INT,
    DocFile BLOB
);

---

6. Other Special Data Types

Some databases offer additional data types:

• ENUM – A predefined list of possible values.
  Example: ENUM('Small', 'Medium', 'Large').
• SET – Stores multiple values from a predefined set.
• JSON – Stores JSON formatted data.
• XML – Stores XML data.

Example:

CREATE TABLE Products (
    Size ENUM('Small', 'Medium', 'Large'),
    Attributes JSON
);

---

Choosing the Right Data Type

When deciding which data type to use, consider:

1. Nature of the Data – Is it a number, text, date, or binary?
2. Range of Values – Choose a type that can store the largest expected value.
3. Precision Needs – For exact calculations, use DECIMAL instead of FLOAT.
4. Storage Space – Smaller types save storage and improve performance.
5. Database Compatibility – Not all types are available in every database (MySQL, PostgreSQL, SQL Server, etc.).

---

Example – Creating a Table with Different Data Types

    CREATE TABLE CustomerOrders (
    OrderID INT PRIMARY KEY,
    CustomerName VARCHAR(50),
    Email VARCHAR(100),
    OrderDate DATE,
    OrderAmount DECIMAL(8, 2),
    IsPaid BOOLEAN,
    InvoiceFile BLOB
);

In this example:

• OrderID → Integer
• CustomerName, Email → Strings
• OrderDate → Date type
• OrderAmount → Decimal type for money
• IsPaid → Boolean
• InvoiceFile → Binary data

---

Conclusion

Understanding SQL Data Types is crucial for designing efficient and reliable databases.
By selecting the right data type, you can:

• Store data accurately
• Reduce storage costs
• Speed up queries
• Maintain data integrity

Always choose the most specific and smallest possible data type that fits your needs. Whether you are storing numbers, text, dates, or binary files, SQL has a suitable data type for the job.