SQL Data Types Explained with Examples

SQL Data Types are the foundation of every relational database. Database administrators classify data storage restrictions by determining each column’s permitted file types and specific data formats, including number values, text entries, date records, and binary file options.

All professionals, including developers, must grasp SQL data types because they maintain data integrity while enhancing storage optimization and query efficiency.

This article details every primary SQL data type through practical examples while guiding the selection of appropriate data types according to specific requirements.

Also read: SQL Commands | DDL, DQL, DML, DCL and TCL Commands

Why SQL Data Types Matter

Database system efficiency and accuracy depend significantly on choosing appropriate SQL data types when designing the schema.

Here’s why they matter:

• The database structure with data integrity mechanisms prevents the entry of incompatible data (for example, text in numerical fields) into the system.
• The database efficiently saves storage space because it provides the allocated space needed.
• Database queries run faster along with indexing because accurate and compact data types reduce their size while improving performance.
• A few benefits of application compatibility come from when data types maintain alignment, which allows applications to connect with the database effectively.

Using the DECIMAL data type instead of FLOAT helps prevent rounding errors, so financial professionals from the banking or e-commerce industry can benefit significantly.

Main Categories of SQL Data Types

SQL Data Types are grouped into the following major categories:

1. Numeric Data Types
2. Character and String Data Types
3. Date and Time Data Types
4. Binary Data Types
5. Boolean Data Types
6. Special Data Types (like XML and Spatial)

Let’s break down each type in detail.

Also read: What is SQL? How to Write Clean and Correct SQL Commands for Beginners

1. Numeric Data Types

Numeric data types store numbers and allow arithmetic operations.

1.1 Exact Numeric Types

Data Type	Description	Range
TINYINT	Very small integers	0 to 255
SMALLINT	Small integers	-32,768 to 32,767
INT	Standard integer values	-2,147,483,648 to 2,147,483,647
BIGINT	Large integers	±9.2 quintillion
DECIMAL	Fixed precision numbers (e.g., for currency)	Up to 38 digits
NUMERIC	Same as DECIMAL	Up to 38 digits
MONEY	Monetary values	±922 trillion
SMALLMONEY	Smaller range of monetary values	±214 thousand

Here is an SQL CREATE TABLE statement that demonstrates the use of all the exact numeric data types.

CREATE TABLE ExactNumericExamples (
    id INT PRIMARY KEY,                        -- Unique identifier for each row
    age TINYINT,                               -- Very small integer (0–255)
    number_of_items SMALLINT,                  -- Small integer (-32,768 to 32,767)
    total_orders INT,                          -- Standard integer
    total_customers BIGINT,                    -- Large integer (for massive user base)
    product_price DECIMAL(10, 2),              -- Currency value like 99999999.99
    discount NUMERIC(5, 2),                    -- Percentage discount like 12.50
    annual_revenue MONEY,                      -- Large money values
    monthly_subscription SMALLMONEY            -- Smaller money values
);

✅ Explanation of Columns:

Column Name	Data Type	Example Value	Purpose
id	INT	1	Primary key (auto or manual ID)
age	TINYINT	25	Suitable for small values like age
number_of_items	SMALLINT	500	Good for countable items
total_orders	INT	1,000,000	Regular-sized count or totals
total_customers	BIGINT	9,000,000,000	Massive numbers (e.g., global users)
product_price	DECIMAL	49.99	Precise price data
discount	NUMERIC	15.75	Accurate decimal percentage
annual_revenue	MONEY	850000000.00	Financial values
monthly_subscription	SMALLMONEY	19.99	Monthly subscription pricing

1.2 Approximate Numeric Types

Data Type	Description	Range
FLOAT	Floating-point numbers	±1.79E+308
REAL	Lower-precision float values	±3.40E+38

Here’s a sample SQL table using the Approximate Numeric Data Types FLOAT and REAL:

CREATE TABLE ApproximateNumericExamples (
    id INT PRIMARY KEY,                  -- Unique identifier
    temperature_reading FLOAT,           -- High-precision float value
    humidity_percentage REAL             -- Lower-precision float value
);

✅ Explanation of Columns:

Column Name	Data Type	Example Value	Purpose
id	INT	1	Unique ID for each record
temperature_reading	FLOAT	98.6254	Used for scientific or sensor readings
humidity_percentage	REAL	75.3	Used when slightly lower precision is acceptable

2. Character and String Data Types

Used for storing text, names, descriptions, etc.

2.1 Non-Unicode Types

Data Type	Description	Max Length
CHAR(n)	Fixed-length non-Unicode string	Up to 8000 characters
VARCHAR(n)	Variable-length non-Unicode string	Up to 8000 characters
VARCHAR(MAX)	Variable-length (large) non-Unicode string	Up to 2GB
TEXT	Large variable-length text	Up to 2GB

Here’s an example SQL table using the character string data types: CHAR, VARCHAR, VARCHAR(MAX), and TEXT:

CREATE TABLE CharacterStringExamples (
    id INT PRIMARY KEY,                          -- Unique identifier
    country_code CHAR(2),                        -- Fixed-length code (e.g., "US")
    city_name VARCHAR(100),                      -- Variable-length short text
    description VARCHAR(MAX),                    -- Very long text data (non-Unicode)
    notes TEXT                                   -- Legacy long text storage
);

✅ Explanation of Columns:

Column Name	Data Type	Example Value	Purpose
id	INT	1	Unique ID for each row
country_code	CHAR(2)	‘IN’	Always stores 2 characters, ideal for standard codes
city_name	VARCHAR(100)	‘San Francisco’	Stores city names of varying lengths
description	VARCHAR(MAX)	(Very long text)	Stores large content like summaries, comments, blog entries
notes	TEXT	(Large legacy text)	Stores large content like summaries, comments, and blog entries

2.2 Unicode Types

Data Type	Description	Max Length
NCHAR(n)	Fixed-length Unicode string	Up to 4000 characters
NVARCHAR(n)	Variable-length Unicode string	Up to 4000 characters
NVARCHAR(MAX)	Large Unicode text	Up to 2GB

Here’s an SQL table example using the Unicode character string data types: NCHAR, NVARCHAR, and NVARCHAR(MAX):

CREATE TABLE UnicodeStringExamples (
    id INT PRIMARY KEY,                             -- Unique identifier
    language_code NCHAR(2),                         -- Fixed-length Unicode (e.g., 'EN')
    city_name NVARCHAR(100),                        -- Variable-length Unicode string
    description NVARCHAR(MAX)                       -- Large Unicode text
);

✅ Explanation of Columns:

Column Name	Data Type	Example Value	Purpose
id	INT	1	Unique ID for each entry
language_code	NCHAR(2)	‘JP’	Stores language/country code using fixed Unicode characters
city_name	NVARCHAR(100)	‘München’	Stores language/country codes using fixed Unicode characters
description	NVARCHAR(MAX)	(Multilingual text)	Store names that may include special or international characters

3. Date and Time Data Types

These types store temporal data such as timestamps and dates.

Data Type	Description	Storage Size
DATE	Stores year, month, and day	3 Bytes
TIME	Stores hour, minute, and second	3 Bytes
DATETIME	Stores full date and time	8 Bytes

📌 Use Case: Use DATETIME to log when a user logs in or places an order.

Here’s an example SQL table using the DATE, TIME, and DATETIME data types:

CREATE TABLE EventSchedule (
    event_id INT PRIMARY KEY,         -- Unique ID for each event
    event_name VARCHAR(100),          -- Name of the event
    event_date DATE,                  -- Date of the event
    event_time TIME,                  -- Time when the event starts
    created_at DATETIME               -- Timestamp when the record was created
);

✅ Explanation of Columns:

Column Name	Data Type	Example Value	Purpose
event_id	INT	1	Unique identifier for the event
event_name	VARCHAR(100)	‘Webinar on SQL’	Name or title of the event
event_date	DATE	‘2025-05-10’	Stores the date portion only
event_time	TIME	’14:30:00′	Stores the time portion only
created_at	DATETIME	‘2025-04-28 10:15:00’	Stores full date and time (timestamp)

4. Binary Data Types

Used to store binary objects like images, documents, and multimedia.

Data Type	Description	Max Length
BINARY(n)	Fixed-length binary data	Up to 8000 bytes
VARBINARY	Variable-length binary data	Up to 8000 bytes
IMAGE	Binary data for large files	Up to 2GB

📌 Use Case: Use VARBINARY(MAX) to store user-uploaded profile images or PDFs.

Here’s an example SQL table using the BINARY, VARBINARY, and IMAGE data types:

CREATE TABLE FileStorage (
    file_id INT PRIMARY KEY,               -- Unique file identifier
    file_name VARCHAR(255),                -- Name of the file
    file_signature BINARY(16),             -- Fixed-length binary, e.g., file hash
    file_data VARBINARY(8000),             -- Variable-length binary, e.g., small documents
    file_image IMAGE                       -- Large binary data, e.g., full-size image or media
);

✅ Explanation of Columns:

Column Name	Data Type	Example Use	Description
file_id	INT	101	Unique ID for each file
file_name	VARCHAR(255)	‘invoice.pdf’	Name of the file
file_signature	BINARY(16)	Binary hash or checksum	Fixed-length binary value (e.g., MD5 hash)
file_data	VARBINARY	Small image or document data	Variable-length binary data (up to 8000 bytes)
file_image	IMAGE	High-resolution images, videos, etc.	Stores large binary data (up to 2 GB)

5. Boolean Data Type

Used to store logical values.

Data Type	Description
BOOLEAN	Stores TRUE or FALSE values

Here’s an example SQL table using the BOOLEAN data type:

CREATE TABLE UserPreferences (
    user_id INT PRIMARY KEY,          -- Unique identifier for each user
    username VARCHAR(50),             -- User's name
    email_notifications BOOLEAN,      -- TRUE = enabled, FALSE = disabled
    dark_mode BOOLEAN                 -- TRUE = dark mode on, FALSE = off
);

✅ Explanation of Columns:

Column Name	Data Type	Description
user_id	INT	Unique ID for each user
username	VARCHAR	Stores the username
email_notifications	BOOLEAN	Indicates if the user wants email notifications
dark_mode	BOOLEAN	Indicates if dark mode is enabled for the user

6. Special SQL Data Types

These are used for advanced data storage and querying.

6.1 XML Data Type

Data Type	Description
XML	Stores and queries XML documents

📌 Use Case: Store and manipulate structured documents in applications like CRMs or APIs.

CREATE TABLE ProductSpecs (
    product_id INT PRIMARY KEY,       -- Unique identifier for each product
    product_name VARCHAR(100),        -- Name of the product
    specifications XML                -- XML data storing product specifications
);

✅ Explanation of Columns:

Column Name	Data Type	Description
product_id	INT	Unique ID for each product
product_name	VARCHAR	Stores the name of the product
specifications	XML	Stores detailed product specs in XML format

6.2 Spatial Data Types

Used for geographic or geometric data.

Data Type	Description
GEOMETRY	Stores shapes like points, lines, and polygons

📌 Use Case: In location-based apps to store coordinates, maps, or regions.

Here’s an example SQL table using the GEOMETRY data type:

CREATE TABLE CityMap (
    location_id INT PRIMARY KEY,      -- Unique identifier for each location
    name VARCHAR(100),                -- Name of the place or landmark
    area GEOMETRY                     -- Spatial data such as point, line, or polygon
);

✅ Explanation of Columns:

Column Name	Data Type	Description
location_id	INT	Unique ID for the geographic feature
name	VARCHAR	Name of the location or area
area	GEOMETRY	Stores spatial representation (e.g., shape)

🗺️ Example GEOMETRY Data:

• A point: POINT(30 10)
• A line: LINESTRING(30 10, 10 30, 40 40)
• A polygon: POLYGON((30 10, 40 40, 20 40, 10 20, 30 10))

Choosing the Right SQL Data Type

Here are quick tips for selecting the best data type:

• Use INT for counting or ID columns.
• Use DECIMAL for currency values, not FLOAT.
• Use VARCHAR for flexible-length strings; CHAR for fixed codes.
• Use NVARCHAR for multilingual support.
• Use correct date/time types for scheduling and logging.
• Use BOOLEAN for true/false flags or status fields.

🚫 Avoid using overly large types (like TEXT or IMAGE) unless necessary, as they can slow down performance.

SQL Data Types Comparison Table

Category	Data Type Examples	Use Case Example
Numeric	INT, BIGINT, DECIMAL, FLOAT	Order ID, Price, Scientific Data
String	VARCHAR, NVARCHAR, TEXT	Names, Descriptions, Multilingual Data
Date & Time	DATE, TIME, DATETIME	Events, Log Time, Birthday
Binary	VARBINARY, IMAGE	Images, Files
Boolean	BOOLEAN	Active status, Yes/No Flags
Special	XML, GEOMETRY	XML storage, GPS Data

Conclusion

Reliable database systems require SQL Data Types to operate effectively. Your database’s performance, integrity, and functionality depend heavily on selecting appropriate data types when handling customer names and payment processing alongside timestamp logging.

Correctly using SQL data types enables you to establish databases that resist failure and sustain into the future.

Real-world scenarios should be used to practice data types to develop confidence levels, resulting in improved database building.

Read More

• Learning SQL (Generate, Manipulate, Retrieve Data) – Alan Beaulieu
• Practical SQL (2nd Edition) – Anthony DeBarros
• Python Programming and SQL Bible – 7 Books in 1 – Oles Aleksey
• SQL Queries for Mere Mortals – John Viescas