SQL INT Data Types in Different Databases

INT is short for integer, and is a group of data types in many databases that store numbers. There are many INT data types in the major SQL databases.

In this guide, you’ll see all of these INT data types, see their restrictions, and how they compare between databases.

Let’s take a look.

SQL INT Data Types

We’ll look at four main SQL databases in this guide: Oracle, SQL Server, MySQL, and PostgreSQL. Each of them have slightly different INT data types and it can be confusing to remember which data type does what.

So, in this guide, we’ll look at all of them, as well as a comparison table.

For more information on all of the different data types in these databases, check out this post on SQL Data Types.

 

SQL Server INT Data Types

Here are the INT data types that exist in SQL Server:

Data Type	Range	Storage
TINYINT	0 to 255	1 byte
SMALLINT	-32,768 to 32,767	2 bytes
INT	-2,147,483,648 to 2,147,483,647	4 bytes
BIGINT	-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807	8 bytes

The primary integer data type is INT. This is the most common one that I have seen in my own code and in examples online. It stores up to about 2 billion, which is big enough for a lot of numerical data.

It’s good practice to use the smallest data type that can contain the possible values you might need. Sometimes, this may be TINYINT, if you expect less than 255 values.

Note that the word INTEGER is a synonym for INT. This means if you use INTEGER, it’s translated by the database to mean INT.

Example

Here’s an example of creating a table with these data types and inserting values into them.

CREATE TABLE sql_server_int (
tinyint_col TINYINT,
smallint_col SMALLINT,
int_col INT,
bigint_col BIGINT
);

INSERT INTO sql_server_int (tinyint_col, smallint_col, int_col, bigint_col)
VALUES (255, 32767, 2147483647, 9223372036854775807);

The values are inserted into the table.

 

MySQL INT Data Types

MySQL has several integer data types:

Data Type	Range (Unsigned)	Range (Signed)	Storage
TINYINT	0 to 255	-128 to 127	1 byte
SMALLINT	0 to 65,535	-32,768 to 32,767	2 bytes
MEDIUMINT	0 to 16777215	-8,388,608 to 8,388,607	3 bytes
INT	0 to 4,294,967,295	-2,147,483,648 to 2,147,483,647	4 bytes
BIGINT	0 to 18,446,744,073,709,551,616	-9,223,372,036,854,775,807 to 9,223,372,036,854,775,807	8 bytes

MySQL is the only vendor of these four to have unsigned INT data types. An unsigned data type means there is no indicator for whether it’s positive or negative, so all values are positive (or 0). This means the range is higher.

Example

Here’s an example of creating a table and inserting data using MySQL INT data types:

CREATE TABLE mysql_int (
tinyint_col TINYINT,
smallint_col SMALLINT,
mediumint_col MEDIUMINT,
int_col INT,
bigint_col BIGINT
);

INSERT INTO mysql_int (tinyint_col, smallint_col, mediumint_col, int_col, bigint_col)
VALUES (127, 32767, 8388607, 2147483647, 9223372036854775807);

 

Also, here’s an example of using UNSIGNED integers in MySQL. You simply add the word UNSIGNED after declaring the data type.

CREATE TABLE mysql_int_unsigned (
tinyint_col TINYINT UNSIGNED,
smallint_col SMALLINT UNSIGNED,
mediumint_col MEDIUMINT UNSIGNED,
int_col INT UNSIGNED,
bigint_col BIGINT UNSIGNED
);

INSERT INTO mysql_int_unsigned (tinyint_col, smallint_col, mediumint_col, int_col, bigint_col)
VALUES (255, 65535, 16777215, 4294967295, 18446744073709551616);

 

Oracle INT Data Types

Here are the INT data types in Oracle SQL:

Data Type	Range	Storage
NUMBER	– 1 x 10^-130 to 9.99…9 x 10^125	Up to 21 bytes

Oracle really only has the NUMBER data type to store integers. It stores up to 38 digits and uses up to 21 bytes to store the number, so it’s quite flexible.

The NUMBER data type can also be used to store decimal numbers or numbers with decimal places.

There are several other keywords or data types in Oracle, but they are all translated to NUMBER:

• INTEGER
• FLOAT
• DECIMAL
• NUMERIC
• INT
• SMALLINT

Example

Here’s an example of creating and inserting a NUMBER data type in Oracle:

CREATE TABLE oracle_int (
number_col NUMBER
);

INSERT INTO oracle_int (number_col)
VALUES (18446744073709551616);

 

PostgreSQL INT Data Types

PostgreSQL has a few INT data types:

Data Type	Range	Storage
SMALLINT	-32,768 to 32,767	2 bytes
INTEGER	-2,147,483,648 to 2,147,483,647	4 bytes
BIGINT	-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807	8 bytes

These three data types are similar to other databases. The type of INTEGER is often recommended as it stores a range of values that’s usually enough. INTEGER is the same as INT in MySQL and SQL Server.

Example

Here’s an example of creating and insert INT data types in PostgreSQL:

CREATE TABLE postgresql_int (
smallint_col SMALLINT,
integer_col INTEGER,
bigint_col BIGINT
);

INSERT INTO sql_server_int (smallint_col,integer_col, bigint_col)
VALUES (32767,2147483647, 9223372036854775807);

 

Comparison

Now we’ve looked at the different data types in different databases, how do they compare between them? This table shows the data types in each SQL database:

Data Type	Range	SQL Server	MySQL	Oracle	PostgreSQL
TINYINT*	-128 to 127	Yes	Yes
SMALLINT	-32,768 to 32,767	Yes	Yes		Yes
MEDIUMINT	-8,388,608 to 8,388,607		Yes
INT or INTEGER	-2,147,483,648 to 2,147,483,647	Yes	Yes		Yes
BIGINT	-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807	Yes	Yes		Yes
NUMBER	– 1 x 10^-130 to 9.99…9 x 10^125			Yes

The exception to TINYINT is SQL Server is only unsigned, so it stores a range of 0 to 255.

So, there’s how the different integer data types compare in different SQL vendors. Most of them share a set of common data types, with Oracle being the exception.

Do you have any questions on these data types? Share them in the comments below.