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.
in oracle you can specify size and precision to mimic int of any size. e.g. script. from oracle
SQL>
SQL> set echo on;
SQL> COLUMN bigger_int CLEAR;
SP2-0046: COLUMN ‘bigger_int’ not defined
SQL>
SQL> set num 15;
SQL> drop table a_dbf cascade constraints;
Table A_DBF dropped.
SQL> create table a_dbf (small_int number(3,0) , bigger_int number(12,0))
2 ;
Table A_DBF created.
SQL> insert into a_dbf(small_int) values(-999);
1 row inserted.
SQL> insert into a_dbf(small_int) values(999);
1 row inserted.
SQL> insert into a_dbf(small_int) values(0);
1 row inserted.
SQL> insert into a_dbf(bigger_int)
2 values(-123456789012);
1 row inserted.
SQL> insert into a_dbf(bigger_int)
2 values(123456789012);
1 row inserted.
SQL>
SQL> select * from a_dbf order by 1,2;
SMALL_INT BIGGER_INT
————— —————
-999
0
999
-123456789012
123456789012
I find it incredibly difficult to understand “engineer” speak – Ben’s descriptions and examples are very clear and easy to understand. The biggest difference is that he is not adding in every.single.statement. to make his point. Just what is needed for clarity.
I did not read the comment above, since I would have to wade through so much extra stuff. Please consider that while we love your proficiency and completeness, we are looking for simple, easy-to-digest and easy-to-understand. Thank you… I can’t tell you how much I and many others would appreciate it.
@Ben, many thanks for your good work. You’re pretty inspiring. ML
Thanks for the kind words Meryl! Glad you like my descriptions and explanations!