SQL Data Types: Choose the Right Type for Your Database

What are SQL Data Types?

A SQL data type specifies the type of data that a column can hold. For example, a column storing ages needs a numeric data type. A column for names needs a text data type.

SQL data types are important because they help in:

• Data Integrity: They prevent you from putting the wrong kind of data into a column. You cannot store text in a number column.
• Storage Efficiency: Different data types use different amounts of storage space. Choosing the right type saves disk space.
• Performance: Databases can process queries faster when they know the data type. This improves overall speed.
• Data Validation: Data types act as an initial check. They ensure data fits the expected format.

Numeric Data Types in SQL

Numeric data types store various kinds of numbers. You use them for integers, decimals, and floating-point numbers.

1. INT (Integer)

The INT data type stores whole numbers without a decimal point. It’s common for IDs, counts, and other non-fractional values.

Syntax:

column_name INT

Example:

You create a table called Products. This table has an id column for product identification.

CREATE TABLE Products (
    id INT,
    product_name VARCHAR(255)
);

You insert a whole number into the id column.

INSERT INTO Products (id, product_name) VALUES (101, 'Laptop');

2. SMALLINT

SMALLINT stores small whole numbers. It uses less storage than INT. Use it when you know numbers will stay within a smaller range.

Syntax:

column_name SMALLINT

Example:

You create a table for Employees. It has an age column. Ages do not usually exceed 127.

CREATE TABLE Employees (
    employee_id INT,
    employee_name VARCHAR(255),
    age SMALLINT
);

You insert a small integer into the age column.

INSERT INTO Employees (employee_id, employee_name, age) VALUES (1, 'Alice', 30);

3. TINYINT

TINYINT stores very small whole numbers. It saves even more space than SMALLINT. Use it for values like true/false (0 or 1) or small counts.

Syntax:

column_name TINYINT

Example:

You create a Settings table. It has an is_active column to store 0 or 1.

CREATE TABLE Settings (
    setting_id INT,
    setting_name VARCHAR(255),
    is_active TINYINT
);

You insert a tiny integer into the is_active column.

INSERT INTO Settings (setting_id, setting_name, is_active) VALUES (1, 'Notifications', 1);

4. BIGINT

BIGINT stores very large whole numbers. Use it for values that exceed the range of INT. This is common for large-scale IDs or very high counts.

Syntax:

column_name BIGINT

Example:

You create a LogEntries table for system logs. It has a timestamp_id column. This stores large numerical timestamps.

CREATE TABLE LogEntries (
    log_id BIGINT,
    event_description VARCHAR(255)
);

You insert a large integer into the log_id column.

INSERT INTO LogEntries (log_id, event_description) VALUES (9223372036854775807, 'System shutdown');

5. DECIMAL (DEC) / NUMERIC

DECIMAL or NUMERIC stores numeric values with a fixed precision and scale. Precision is the total number of digits. Scale is the number of digits after the decimal point. Use these for financial data where accuracy is critical.

Syntax:

column_name DECIMAL(precision, scale)
column_name NUMERIC(precision, scale)

Example:

You create an Orders table. It has a total_amount column. This stores currency values with two decimal places.

CREATE TABLE Orders (
    order_id INT,
    item_name VARCHAR(255),
    total_amount DECIMAL(10, 2)
);

You insert a decimal value into the total_amount column.

INSERT INTO Orders (order_id, item_name, total_amount) VALUES (1, 'Book', 29.99);

6. FLOAT / REAL

FLOAT and REAL store approximate floating-point numbers. They are not exact. Use them when precision is not as critical, like scientific calculations. REAL offers single precision, while FLOAT offers double precision.

Syntax:

column_name FLOAT(n) -- n specifies the precision
column_name REAL

Example:

You create a Measurements table. It has a temperature column. This stores temperature readings with decimal values.

CREATE TABLE Measurements (
    measurement_id INT,
    sensor_name VARCHAR(255),
    temperature REAL
);

You insert a floating-point value into the temperature column.

INSERT INTO Measurements (measurement_id, sensor_name, temperature) VALUES (1, 'Room Sensor', 23.5);

7. DOUBLE PRECISION

DOUBLE PRECISION is similar to FLOAT but offers higher precision. Use it for applications requiring more accurate floating-point numbers.

Syntax:

column_name DOUBLE PRECISION

Example:

You create a Coordinates table. It has latitude and longitude columns. These require high precision.

CREATE TABLE Coordinates (
    location_id INT,
    city VARCHAR(255),
    latitude DOUBLE PRECISION,
    longitude DOUBLE PRECISION
);

You insert double precision values into the latitude and longitude columns.

INSERT INTO Coordinates (location_id, city, latitude, longitude) VALUES (1, 'New York', 40.7128, -74.0060);

String Data Types in SQL

String data types store text. You use them for names, addresses, descriptions, and any other textual information.

1. VARCHAR(n) / CHARACTER VARYING(n)

VARCHAR(n) stores variable-length strings. n specifies the maximum number of characters. It saves space because it only uses the storage needed for the actual string length.

Syntax:

column_name VARCHAR(n)
column_name CHARACTER VARYING(n)

Example:

You create a Customers table. It has a customer_name column. This column stores names up to 255 characters.

CREATE TABLE Customers (
    customer_id INT,
    customer_name VARCHAR(255),
    email VARCHAR(100)
);

You insert text into the customer_name and email columns.

INSERT INTO Customers (customer_id, customer_name, email) VALUES (1, 'John Doe', 'john.doe@example.com');

2. CHAR(n) / CHARACTER(n)

CHAR(n) stores fixed-length strings. If a string is shorter than n, the database pads it with spaces to reach n characters. Use it when column values have a consistent length, like country codes.

Syntax:

column_name CHAR(n)
column_name CHARACTER(n)

Example:

You create a Countries table. It has a country_code column. This column always stores 2 characters.

CREATE TABLE Countries (
    country_id INT,
    country_name VARCHAR(255),
    country_code CHAR(2)
);

You insert a fixed-length string into the country_code column.

INSERT INTO Countries (country_id, country_name, country_code) VALUES (1, 'United States', 'US');

3. TEXT

TEXT stores large amounts of text. Its maximum length varies by database system. Use it for descriptions, comments, or blog post content.

Syntax:

column_name TEXT

Example:

You create a BlogPosts table. It has a content column. This stores the full text of a blog post.

CREATE TABLE BlogPosts (
    post_id INT,
    title VARCHAR(255),
    content TEXT
);

You insert long text into the content column.

INSERT INTO BlogPosts (post_id, title, content) VALUES (1, 'Understanding SQL', 'SQL is a powerful language for managing databases...');

4. NCHAR(n) / NVARCHAR(n)

NCHAR and NVARCHAR are similar to CHAR and VARCHAR but store Unicode characters. Use them when you need to store text in multiple languages. N stands for National character set.

Syntax:

column_name NCHAR(n)
column_name NVARCHAR(n)

Example:

You create a MultilingualProducts table. It has a product_name column. This column stores names in various languages.

CREATE TABLE MultilingualProducts (
    product_id INT,
    product_name NVARCHAR(255)
);

You insert Unicode text into the product_name column.

INSERT INTO MultilingualProducts (product_id, product_name) VALUES (1, N'ürün adı'); -- Example for Turkish

5. Character Sets and Collation

Character sets define how text characters are stored. Collation rules determine how characters are sorted and compared. For example, UTF-8 is a common character set that handles most world languages. A collation like utf8_general_ci means comparisons are case-insensitive.

You define character sets and collations at the database, table, or column level.

Example (MySQL):

You create a table with a specific character set and collation for a text column.

CREATE TABLE Articles (
    article_id INT,
    title VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
);

This ensures text is stored using utf8mb4 encoding and sorted based on utf8mb4_unicode_ci rules.

Date and Time Data Types in SQL

Date and time data types store dates, times, or combinations of both. You use them for timestamps, birth dates, and event schedules.

1. DATE

The DATE data type stores a date (year, month, day). It does not include time information.

Syntax:

column_name DATE

Example:

You create an Events table. It has an event_date column. This stores the date of an event.

CREATE TABLE Events (
    event_id INT,
    event_name VARCHAR(255),
    event_date DATE
);

You insert a date into the event_date column.

INSERT INTO Events (event_id, event_name, event_date) VALUES (1, 'Company Picnic', '2025-08-15');

2. TIME

The TIME data type stores a time of day (hour, minute, second). It does not include date information.

Syntax:

column_name TIME

Example:

You create a MeetingSchedule table. It has a start_time column. This stores the starting time of a meeting.

CREATE TABLE MeetingSchedule (
    meeting_id INT,
    topic VARCHAR(255),
    start_time TIME
);

You insert a time into the start_time column.

INSERT INTO MeetingSchedule (meeting_id, topic, start_time) VALUES (1, 'Project Review', '10:30:00');

3. DATETIME / TIMESTAMP

DATETIME and TIMESTAMP store both date and time information. DATETIME stores a fixed date and time. TIMESTAMP stores a value relative to a specific time, like Unix epoch. It also updates automatically in some systems when rows change.

Syntax:

column_name DATETIME
column_name TIMESTAMP

Example:

You create a UserActions table. It has a action_timestamp column. This records when a user performs an action.

CREATE TABLE UserActions (
    action_id INT,
    user_id INT,
    action_description VARCHAR(255),
    action_timestamp DATETIME
);

You insert a date and time into the action_timestamp column.

INSERT INTO UserActions (action_id, user_id, action_description, action_timestamp) VALUES (1, 101, 'Logged in', '2025-07-01 14:30:00');

4. YEAR

The YEAR data type stores a year. Its range is usually from 1901 to 2155.

Syntax:

column_name YEAR

Example:

You create a MovieReleases table. It has a release_year column. This stores the year a movie was released.

CREATE TABLE MovieReleases (
    movie_id INT,
    movie_title VARCHAR(255),
    release_year YEAR
);

You insert a year into the release_year column.

INSERT INTO MovieReleases (movie_id, movie_title, release_year) VALUES (1, 'The Matrix', 1999);

Other Important Data Types in SQL

Beyond numeric, string, and date/time types, other data types serve specific purposes.

1. BOOLEAN (BOOL)

BOOLEAN or BOOL stores true or false values. Many databases use 0 for false and 1 for true.

Syntax:

column_name BOOLEAN
column_name BOOL

Example:

You create a Task table. It has an is_completed column. This indicates if a task is done.

CREATE TABLE Tasks (
    task_id INT,
    task_name VARCHAR(255),
    is_completed BOOLEAN
);

You insert boolean values into the is_completed column.

INSERT INTO Tasks (task_id, task_name, is_completed) VALUES (1, 'Write report', FALSE);
INSERT INTO Tasks (task_id, task_name, is_completed) VALUES (2, 'Submit form', TRUE);

2. BLOB (Binary Large Object)

BLOB stores binary data. This includes images, audio, video, or any other raw data.

Syntax:

column_name BLOB

Example:

You create an Images table. It has an image_data column. This stores actual image files.

CREATE TABLE Images (
    image_id INT,
    image_name VARCHAR(255),
    image_data BLOB
);

You can insert binary data into a BLOB column using specific functions or prepared statements in your application code, not directly with INSERT statements in SQL.

3. ENUM

ENUM allows you to define a column that can only hold one value from a specified list of allowed values.

Syntax:

column_name ENUM('value1', 'value2', 'value3', ...)

Example:

You create an OrderTracking table. It has a status column. This column can only be ‘Pending’, ‘Shipped’, or ‘Delivered’.

CREATE TABLE OrderTracking (
    order_id INT,
    status ENUM('Pending', 'Shipped', 'Delivered')
);

You insert one of the predefined values into the status column.

INSERT INTO OrderTracking (order_id, status) VALUES (101, 'Pending');
INSERT INTO OrderTracking (order_id, status) VALUES (102, 'Shipped');

4. UUID (Universally Unique Identifier) / UNIQUEIDENTIFIER

UUID or UNIQUEIDENTIFIER stores a 128-bit number. It creates a unique identifier across all databases and computers. Use it for primary keys in distributed systems.

Syntax (PostgreSQL, MySQL, SQL Server):

column_name UUID -- PostgreSQL
column_name UNIQUEIDENTIFIER -- SQL Server

Example (PostgreSQL):

You create a Users table. It has a user_id column that stores unique identifiers.

CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; -- Enable UUID generation in PostgreSQL
CREATE TABLE Users (
    user_id UUID DEFAULT uuid_generate_v4(),
    username VARCHAR(255)
);

You insert a new user. The user_id column automatically gets a unique UUID.

INSERT INTO Users (username) VALUES ('AliceSmith');

5. ARRAY

The ARRAY data type stores a list of values of the same type in a single column. Not all SQL databases support this (e.g., PostgreSQL does). Use it when a single record needs multiple related items.

Syntax (PostgreSQL):

column_name data_type[]

Example (PostgreSQL):

You create a ProductTags table. It has a tags column that stores multiple keywords for a product.

CREATE TABLE ProductTags (
    product_id INT,
    product_name VARCHAR(255),
    tags TEXT[]
);

You insert an array of tags.

INSERT INTO ProductTags (product_id, product_name, tags) VALUES (1, 'Smartphone', ARRAY['electronics', 'mobile', 'gadget']);

6. JSON / JSONB

JSON data types store JSON documents. JSONB (binary JSON) stores them in a decomposed binary format, which is more efficient for querying. Use these when you need to store flexible, semi-structured data.

Syntax (PostgreSQL, MySQL):

column_name JSON
column_name JSONB -- PostgreSQL only

Example (PostgreSQL):

You create a UserProfiles table. It has a profile_data column to store varying user information.

CREATE TABLE UserProfiles (
    user_id INT,
    profile_data JSONB
);

You insert JSON data into the profile_data column.

INSERT INTO UserProfiles (user_id, profile_data) VALUES (
    1,
    '{"age": 30, "city": "New York", "interests": ["reading", "hiking"]}'
);

7. Spatial Data Types (e.g., POINT, LINESTRING, POLYGON)

Spatial data types store geographical or geometric information. Databases like PostgreSQL (with PostGIS) and MySQL support them. Use these for mapping applications, location services, or geographical analysis.

Syntax (PostgreSQL with PostGIS):

column_name GEOMETRY(GeometryType, SRID)

Example (PostgreSQL with PostGIS):

You create a Cities table. It has a location column to store geographical coordinates.

CREATE EXTENSION IF NOT EXISTS postgis;
CREATE TABLE Cities (
    city_id INT,
    city_name VARCHAR(255),
    location GEOMETRY(Point, 4326) -- SRID 4326 for WGS 84 (latitude/longitude)
);

You insert a point (longitude, latitude) into the location column.

INSERT INTO Cities (city_id, city_name, location) VALUES (
    1,
    'London',
    ST_SetSRID(ST_MakePoint(-0.1278, 51.5074), 4326)
);

Automatic ID Generation (Auto-increment/Serial)

You often need unique identifiers for each row in a table. SQL databases provide ways to automatically generate these IDs.

1. AUTO_INCREMENT (MySQL)

AUTO_INCREMENT automatically generates a sequential number for each new row. You typically use it with INT or BIGINT for primary keys.

Syntax (MySQL):

column_name INT AUTO_INCREMENT PRIMARY KEY

Example (MySQL):

You create a Posts table. The post_id automatically increases for each new post.

CREATE TABLE Posts (
    post_id INT AUTO_INCREMENT PRIMARY KEY,
    post_title VARCHAR(255),
    post_content TEXT
);

When you insert a row, you do not provide a value for post_id. The database handles it.

INSERT INTO Posts (post_title, post_content) VALUES ('My First Post', 'This is the content of my first post.');

2. SERIAL / BIGSERIAL (PostgreSQL)

SERIAL and BIGSERIAL in PostgreSQL are pseudo-types. They create an integer column that automatically increments. SERIAL uses INT, and BIGSERIAL uses BIGINT.

Syntax (PostgreSQL):

column_name SERIAL PRIMARY KEY
column_name BIGSERIAL PRIMARY KEY

Example (PostgreSQL):

You create a Users table with an auto-incrementing user ID.

CREATE TABLE Users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(255)
);

The user_id column automatically populates when you insert a new user.

INSERT INTO Users (username) VALUES ('JaneDoe');

Setting Default Values

You can set a default value for a column. If you do not provide a value for that column during an insert, the database uses the default. This ensures data consistency.

Syntax:

column_name data_type DEFAULT default_value

Example:

You create a Tasks table. The status column defaults to ‘Pending’, and the created_at column defaults to the current timestamp.

CREATE TABLE Tasks (
    task_id INT PRIMARY KEY,
    task_description VARCHAR(255),
    status VARCHAR(50) DEFAULT 'Pending',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

When you insert a task without specifying status or created_at, they receive their default values.

INSERT INTO Tasks (task_id, task_description) VALUES (1, 'Finish report');

You can verify the default values by selecting the data:

SELECT * FROM Tasks;

This query shows:

Also Read: 40+ SQL Queries with Examples

Best Practices for Using SQL Data Types

Choosing the correct data type is crucial. Follow these best practices:

• Be Specific: Always choose the most specific data type that meets your needs. For example, use SMALLINT instead of INT if the numbers will always be small.
• Consider Storage: Smaller data types save disk space and improve performance. Use TINYINT for 0/1 values.
• Plan for Growth: While being specific, also consider potential future data growth. An INT might be better than SMALLINT if your maximum value could increase significantly.
• Use DECIMAL for Money: Never use FLOAT or DOUBLE PRECISION for financial calculations. They can lead to rounding errors. DECIMAL ensures accuracy.
• Normalize Dates and Times: Store dates and times consistently. Use DATETIME or TIMESTAMP for precise moments.
• Consider Character Sets: For multilingual data, use NVARCHAR or ensure your VARCHAR columns use a character set like UTF-8 and an appropriate collation.
• Validate User Input: Always validate user input in your application before storing it in the database. This adds another layer of protection beyond data types.
• Use Auto-increment for IDs: Leverage AUTO_INCREMENT or SERIAL to manage unique row identifiers efficiently.
• Set Logical Defaults: Use default values to streamline data entry and maintain data consistency.

Conclusion

Understanding SQL data types is important for designing databases. You now know how to select the right data type for numbers, text, dates, and more. This includes specialized types, such as JSON and arrays, as well as crucial concepts like character sets and auto-incrementing IDs. This knowledge will help you build efficient and reliable databases.

Suggested Resources to Read:

• SQL Commands with Examples
• SQL Tutorial for Beginners
• Normalization in SQL
• SQL Joins – Inner, Left, Right & Full Join