SQL Data Types

Summary: In this tutorial, you’ll learn the most common SQL data types, including string, numeric, and date and time types, and when to use each one.

What are SQL data types? #

In a database, every column of a table has a specific data type. A data type specifies the type of values that a column can store, such as strings, numbers, and dates.

For example:

• The first name column stores strings.
• The salary column stores numbers.
• The date of birth column stores date values.
• The created-at column stores both date and time.

When designing tables, you should choose the right SQL data type because it affects:

• The kind of data a column can store
• Storage
• Data validation & accuracy

Most Common SQL Data Types #

SQL organizes the data into the following main groups:

String data types #

• CHAR
• VARCHAR

Numeric data types #

• INT
• SMALLINT
• BIGINT
• DECIMAL
• FLOAT
• REAL
• DOUBLE PRECISION

Data and time data types #

• DATE
• TIME
• TIMESTAMP

Note that the exact syntax may vary slightly depending on the database system, such as MySQL, PostgreSQL, SQL Server, or Oracle.

SQL String Data Types #

String data types store text values such as names, job titles, and department names.

SQL has the following common string data types:

• CHAR
• VARCHAR

CHAR #

CHAR stores a fixed-length string.

Here’s the syntax

CHAR (n)Code language: SQL (Structured Query Language) (sql)

In this syntax, n is a fixed number of characters that the column stores. The n parameter is optional. If you skip it, the database uses 1 by default.

So the following declaration:

CHARCode language: SQL (Structured Query Language) (sql)

is equivalent to the following:

CHAR (1)Code language: SQL (Structured Query Language) (sql)

The maximum value of n depends on the specific implementation of the database system.

Note that CHAR and CHARACTER are equivalent:

CHARACTER(n)Code language: SQL (Structured Query Language) (sql)

The following defines a column with the fixed-length character data type, which can store up to five characters:

column_name CHARACTER(5)Code language: SQL (Structured Query Language) (sql)

If you store a string whose length is two in the column above, then the database system will pad the three spaces to the string to ensure that each value in a column has a fixed length of five.

An example of using the fixed-length character data type is to store state abbreviations because all state abbreviations are two characters, e.g., CA, NY, and TX.

When to use CHAR #

Use CHAR for fixed-length values such as:

• Country codes like US, FR, DE
• State abbreviations like CA, TX
• Fixed-length status flags

VARCHAR #

VARCHAR stores varying-length strings.

Here’s the syntax:

VARCHAR(n)Code language: SQL (Structured Query Language) (sql)

In this syntax, n represents the maximum number of characters that the column can store. Some database systems allow you to specify the number of bytes that the column can store.

The following example defines a column with the VARCHAR data type:

first_name VARCHAR(50)Code language: SQL (Structured Query Language) (sql)

If you store a value whose length is 20 in the first_name column, the database stores that value without padding it to the full length.

However, if you store a value with a length greater than 50, the database may issue an error.

When to use VARCHAR #

In practice, you use VARCHAR when the string length varies, for example:

• first names
• email addresses
• product names

CHAR vs VARCHAR #

When you start learning SQL data types, you often find difficulty in choosing between CHAR and VARCHAR. Here are some tips:

Use CHAR when:

• The value length is fixed.
• All values have the same length.

Use VARCHAR when:

• The value length varies
• All values do not have the same length.

In practice, VARCHAR is more common than CHAR.

SQL Numeric Data Types #

Numeric data type stores numbers such as age and salary.

Here are the most common SQL numeric data types:

• SMALLINT
• INT
• BIGINT
• DECIMAL
• NUMERIC
• FLOAT
• REAL
• DOUBLE PRECISION

DECIMAL and NUMERIC #

The DECIMAL and NUMERIC data type stores exact numeric values. Typically, you use these types for:

• Salaries
• Prices
• and any number where precision matters

Here’s the syntax for defining a column with the DECIMAL data type:

column_name DECIMAL (p,s)Code language: SQL (Structured Query Language) (sql)

In this syntax:

• p is the precision, or total number of digits.
• s is the scale, or number of digits after the decimal point.

The maximum values of p and s depend on the implementation of each database system.

The following defines the salary column with 12 digits, which includes 4 digits after the decimal point:

salary DECIMAL (12,4)Code language: SQL (Structured Query Language) (sql)

The salary column can store a number with a value up to 99,999,999.9999

When to use DECIMAL #

In practice, you use DECIMAL to store values that must be exact, especially for:

• Money
• Financial calculations
• Measurement
  

Integer types #

Integer types store whole numbers, without decimal places.

Here are common integer types:

• SMALLINT – smaller whole numbers.
• INT or INTEGER – standard whole numbers.
• BIGINT – very big whole numbers.

Here’s an example:

employee_id INTCode language: SQL (Structured Query Language) (sql)

When to use integer types #

In practice, you use integer types for values such as:

• ID
• Ages
• Ranking
• Quantities

Floating-point data types #

The floating-point data types represent approximate numeric values.

Here are common floating-point types:

• FLOAT
• REAL
• DOUBLE PRECISION

You use Floating-point data types for values where a small rounding difference is acceptable. For example:

temperature FLOATCode language: SQL (Structured Query Language) (sql)

If you store money values, you should use DECIMAL instead of FLOAT because FLOAT stores approximate values that can cause rounding differences.

DECIMAL vs FLOAT #

Use DECIMAL to store exact values and FLOAT to store approximate values.

SQL Date and Time Data Types #

Date and time data types store calendar dates, times, or both.

Here are the most common SQL data/time types:

• DATE
• TIME
• TIMESTAMP

DATE #

The DATE store a calendar date that includes:

• year
• month
• day.

Typically, the range of the DATE The data type is from 0001-01-01 to 9999-12-31.

The date value is generally specified in the form:

'YYYY-MM-DD'Code language: SQL (Structured Query Language) (sql)

For example, the following DATE value is December 31, 2020:

'2020-12-31' Code language: SQL (Structured Query Language) (sql)

When to use DATE #

You use DATE for the date-only values, for example:

• birth dates
• hire dates

TIME #

The TIME data type stores values representing a time of day, including

• hours
• minutes
• seconds.

The TIME values should be specified in the following form:

'HH:MM:SS'Code language: SQL (Structured Query Language) (sql)

Some database systems support fractional seconds like this:

'10:59:30.9999'Code language: SQL (Structured Query Language) (sql)

When to use TIME #

You use TIME to store time-only values like:

• shift start times
• meeting times
• opening hours

TIMESTAMP #

The TIMESTAMP stores both a date and a time.

A TIMESTAMP value is typically written like this:

TIMESTAMP 'YYYY-MM-DD HH:MM:SS'Code language: SQL (Structured Query Language) (sql)

Notice that there is a space separator between the date and time parts.

When to use TIMESTAMP #

You use TIMESTAMP for values that require both date and time, for example:

• Creation time
• Event logs
• Last updated time

Summary #

• SQL data types define what kind of values a column can store.
• Use CHAR type for fixed-length text.
• Use VARCHAR type for variable-length text.
• Use INT type for whole numbers.
• Use DECIMAL type for exact values such as money.
• Use FLOAT type for approximate numeric values.
• Use DATE type for dates only.
• Use TIME type for time only.