SQL: Data Types

1. Introduction to SQL Data Types

Data types in SQL define what kind of data can be stored in a table column — numbers, text, dates, binary, etc.
The type you choose affects:

Storage space
Performance
Validation (ensuring only valid data is stored)

2. Main Categories of SQL Data Types

A. Numeric Data Types

Used for numbers — integers, decimals, and floating-point.

Data Type	Description	Example
`INT` / `INTEGER`	Whole numbers (−2,147,483,648 to 2,147,483,647)	100
`SMALLINT`	Smaller whole numbers (−32,768 to 32,767)	200
`TINYINT`	Very small whole numbers (0 to 255)	45
`BIGINT`	Very large whole numbers	9223372036854775807
`DECIMAL(p,s)` / `NUMERIC(p,s)`	Fixed-point decimal with precision `(p)` and scale `(s)`	123.45
`FLOAT(n)`	Approximate floating-point number	3.14159
`REAL` / `DOUBLE PRECISION`	Double-precision floating-point number	2.718281828

B. String (Character) Data Types

Used for text and character-based data.

Data Type	Description	Example
`CHAR(n)`	Fixed-length string, pads with spaces if shorter	'SQL '
`VARCHAR(n)`	Variable-length string (max length `n`)	'Database'
`TEXT`	Large text data (varies by SQL dialect)	'This is a long article...'
`NCHAR(n)`	Fixed-length Unicode string	'भारत'
`NVARCHAR(n)`	Variable-length Unicode string	'مرحبا'
`NTEXT`	Large Unicode text (deprecated in some versions)	'Unicode text data'

C. Date and Time Data Types

Used for storing dates, times, and timestamps.

Data Type	Description	Example
`DATE`	Date only (`YYYY-MM-DD`)	2025-08-08
`TIME`	Time only (`HH:MM:SS`)	15:45:30
`DATETIME`	Date and time	2025-08-08 15:45:30
`TIMESTAMP`	UTC timestamp (auto-updates in some DBs)	2025-08-08 10:15:00
`YEAR`	Year (2 or 4 digits)	2025

D. Binary Data Types

Used for storing raw binary data (e.g., images, files).

Data Type	Description	Example
`BINARY(n)`	Fixed-length binary data	0x4F
`VARBINARY(n)`	Variable-length binary data	0x7F9A
`BLOB`	Large binary object	Image, video, PDF

E. Boolean Data Type

Stores TRUE or FALSE values.

Standard SQL: BOOLEAN
MySQL: TINYINT(1) (0 = false, 1 = true)
Example: TRUE / FALSE

F. Special Data Types

(Supported in specific SQL databases)

ENUM – MySQL-specific: predefined set of values
Example: ENUM('Small','Medium','Large')
SET – MySQL-specific: multiple choice from predefined set
Example: SET('A','B','C')
JSON – Stores JSON-formatted data
Example: '{"name":"John","age":30}'
XML – Stores XML data (SQL Server, Oracle)

3. Example: Creating a Table with Various Data Types

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FullName VARCHAR(100),
    BirthDate DATE,
    JoinDate DATETIME,
    Salary DECIMAL(10, 2),
    IsActive BOOLEAN,
    ProfileImage BLOB
);

4. Choosing the Right Data Type

Use smallest size possible for performance & storage savings.
Match data type to data meaning (e.g., don’t store numbers as text).
Use VARCHAR instead of CHAR unless fixed-length strings are required.
Prefer DECIMAL for money to avoid floating-point rounding errors.
Consider timezone handling when storing date/time.

SQL Tutorial Menu