SQL Data Types

When you create a table in SQL, every column must be given a data type. A data type tells the database what kind of value that column will store — a number, a piece of text, a date, or something else.

Think of it like labelling compartments in a drawer. One compartment says "only pens", another says "only coins". SQL data types work the same way — they ensure only the right kind of data goes into each column.

Why Data Types Matter

• They ensure data accuracy — a phone number column will not accidentally store a name.
• They save storage space — using the right type helps the database use memory efficiently.
• They allow calculations — only numeric types can be used in maths operations like addition or average.

1. String (Text) Data Types

String data types store text values — like names, addresses, and email IDs.

When to Use CHAR vs VARCHAR

Use CHAR when the value will always be the same length — for example, a country code like 'IN' or 'US'. Use VARCHAR when the length can vary — for example, a person's name.

In MS SQL Server, always prefer NVARCHAR over VARCHAR because it supports Unicode characters (including regional languages).

2. Numeric Data Types

Numeric data types store numbers — both whole numbers and decimal numbers.

3. Date and Time Data Types

4. Boolean Data Type

A Boolean stores only two values: TRUE or FALSE.

5. Auto-Increment (Auto ID)

When you want the database to automatically assign a unique number to each new row (like a Student ID), both databases offer this feature but with different keywords.

IDENTITY(1,1) means: start from 1, and increase by 1 for every new row.

Complete Table Definition Example

MySQL Query

CREATE TABLE Students (
    StudentID   INT AUTO_INCREMENT PRIMARY KEY,
    StudentName VARCHAR(50),
    Age         INT,
    Fees        DECIMAL(8, 2),
    City        CHAR(30),
    JoinDate    DATE,
    IsActive    BOOLEAN
);

MS SQL Server Query

CREATE TABLE Students (
    StudentID   INT          IDENTITY(1,1) PRIMARY KEY,
    StudentName NVARCHAR(50),
    Age         INT,
    Fees        DECIMAL(8, 2),
    City        NCHAR(30),
    JoinDate    DATE,
    IsActive    BIT          -- 1 = true, 0 = false
);

Key Points

• Always choose the smallest data type that fits your data — it saves storage space.
• Use NVARCHAR in MS SQL Server instead of VARCHAR for Unicode support.
• Use DECIMAL instead of FLOAT when exact values matter, like money or fees.
• MySQL uses BOOLEAN; MS SQL Server uses BIT.
• MySQL uses AUTO_INCREMENT; MS SQL Server uses IDENTITY(1,1).
• The TEXT type is deprecated in MS SQL Server — use NVARCHAR(MAX) instead.
• Date values in SQL are always written in YYYY-MM-DD format.