SQL Data Types Cheat Sheet (2025)

Understanding SQL data types is key to writing effective SQL queries and designing optimal database schemas. This cheat sheet provides a comprehensive reference of SQL data types across various database systems like MySQL, SQL Server, PostgreSQL, etc.

Overview of Common SQL Data Types

SQL data types define the type and size of data that a column or variable can store. Choosing appropriate data types for columns as per the data requirements is crucial while creating tables.

Here‘s a quick overview of the most commonly used SQL data types:

Numeric Data Types: Used to store numerical data including integers, floats, decimals etc. Common examples are INT, BIGINT, FLOAT, DOUBLE, DECIMAL.

Character/String Data Types: Used to store sequence of characters or text data. Common examples are CHAR, VARCHAR, TEXT, BLOB.

Date/Time Data Types: Used to store date, time or timestamp data. Common examples are DATE, TIME, DATETIME, TIMESTAMP.

Other Data Types: Specialized data types like BOOLEAN, BINARY, ARRAY, JSON etc.

In addition to the data type, you can specify additional attributes like size, precision, scale etc. while defining columns.

Now let‘s explore the commonly used data types in detail across some major RDBMS systems.

Numeric Data Types

Numeric data types are used to store numerical data including integer and floating point values.

Here‘s a comparison of numeric data types across MySQL, SQL Server and PostgreSQL:

Data Type	MySQL	SQL Server	PostgreSQL
TINYINT	1 byte integer	1 byte integer	1 byte integer
SMALLINT	2 byte integer	2 byte integer	2 byte integer
MEDIUMINT	3 byte integer	–	–
INT / INTEGER	4 byte integer	4 byte integer	4 byte integer
BIGINT	8 byte integer	8 byte integer	8 byte integer
FLOAT	4 byte floating point	4/8 byte floating point	4/8 byte floating point
DOUBLE	8 byte floating point	8 byte floating point	8 byte floating point
DECIMAL	Variable-precision decimal	Variable-precision decimal	Variable-precision decimal

Key Points:

• Integer data types like TINYINT, SMALLINT, INT store whole numbers.
• Floating point types like FLOAT, DOUBLE store approximate decimal numbers.
• DECIMAL allows storing exact decimal numbers by specifying precision and scale.
• UNSIGNED attribute can be added to allow only positive numbers.
• Different sizes allow optimizing storage for the expected data range.

Character/String Data Types

Character and string data types are used to store sequence of characters, text, JSON etc.

Here‘s a comparison across databases:

Data Type	MySQL	SQL Server	PostgreSQL
CHAR	Fixed length string	Fixed length string	Fixed length string
VARCHAR	Variable length string	Variable length string	Variable length string
TEXT	Variable length string	Variable length string	Variable length string
BLOB	Binary large objects	Binary large objects	Binary large objects

Key Points:

• CHAR pad strings to occupy full allocated storage but VARCHAR does not.
• VARCHAR and TEXT store variable length strings upto specified limit.
• BLOB and TEXT can store upto 4GB and 1GB of data respectively in some databases.

Date and Time Data Types

Date and time related data types allow storing date, time, timestamps and intervals.

Here‘s a comparison across databases:

Data Type	MySQL	SQL Server	PostgreSQL
DATE	Date value	Date value	Date value
TIME	Time value	Time value	Time value with timezone
DATETIME	Date + Time	Date + Time	Timestamp with timezone
TIMESTAMP	Timestamp value	Auto-generated timestamp	Timestamp value
INTERVAL	Time interval	–	Time interval

Key Points:

• DATE stores date in YYYY-MM-DD format.
• TIME stores time in HH:MM:SS format.
• DATETIME and TIMESTAMP store combination of date and time.
• TIMESTAMP can auto record insert/update time in some databases.

Other Common Data Types

There are many other data types offered by different SQL databases for special purposes:

BOOLEAN – Stores boolean or logical values TRUE/FALSE.

ENUM – To store one of predefined text values.

SET – To store multiple text values from predefined set.

ARRAY – Column can store an array of values.

JSON – Column stores data in JSON format.

Choosing the Optimal Data Types

Here are some key considerations while deciding data types for a column:

• Data capacity – Based on the range of data values expected. E.g. INT vs BIGINT.

• Storage size – Balance between storage needs and data capacity. E.g. CHAR vs VARCHAR.

• Decimal precision – For exact decimal number storage e.g. currency.

• Data validation – Data type constraints can validate bad data. E.g. Using ENUM.

• Performance – Data types also impact how indexes, sorts, comparisons etc. are processed.

• Future scope – Additional capacity and attributes to accommodate future data growth.

SQL Data Type Conversion and Casting

In some cases, you may need to convert a value from one data type to another explicitly using type conversion functions or CAST operator.

Here are some common scenarios where data type conversions are useful:

• Storing a numeric calculation result in a varchar column using CAST.
• Comparing or matching string and numeric values using data type conversion.
• Extracting a numeric value from a string using conversion functions.
• Inserting strings or numeric values into date and time data type columns.

Here‘s an example of using CAST to convert a floating point value to a varchar:

SELECT CAST(123.456 AS VARCHAR(10))

And an example to convert string date to a DATETIME:

SELECT CAST(‘2023-01-01‘ AS DATETIME); 

Platform Specific Data Types

Many modern databases also provide additional specialized data types optimized for specific use cases:

Spatial data types in PostgreSQL, SQL Server, MySQL allow storing geospatial coordinate data and running location queries.

JSON data types in latest MySQL, SQL Server, PostgreSQL versions allow optimized storage and query of JSON documents within tables.

Array data types allow storing array of values in a column in PostgreSQL and SQL Server.

HStore key-value pairs in PostgreSQL provide simple NoSQL style storage.

XML data type in SQL Server stores XML data and allows querying on XML properties.

Choosing Data Types in Practice

• Use smallest data type to store the required data range to optimize storage.

• Prefer VARCHAR over CHAR for variable length string columns.

• Use DECIMAL for storing currency or numbers needing precision.

• Use DATE, TIME, DATETIME appropriately rather than VARCHAR/INT.

• Evaluate storage needs for document and LOB data before using BLOB or TEXT.

• Take advantage of specialized data types like JSON if supported.

• Set appropriate collation rules for string data depending on text sorting and comparison needs.

• Add constraints like NOT NULL based on data integrity needs.

Following these data type best practices will ensure you get the right balance of functionality, storage efficiency, data integrity and query performance.

Conclusion

SQL data types form the building blocks while defining database schema during development. Understanding the various numeric, character, date/time and other specialty data types is key to effective use of SQL databases.

This SQL data types cheat sheet summarizes the commonly used data types, considerations for selecting the optimal data types and data type conversion methods across popular SQL databases. Bookmark this guide for reference and ensure you design optimal data type schemas across your SQL Server, MySQL, PostgreSQL and other database projects.