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:

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.

1CREATE TABLE sql_server_int (
2tinyint_col TINYINT,
3smallint_col SMALLINT,
4int_col INT,
5bigint_col BIGINT
6);
7
8INSERT INTO sql_server_int (tinyint_col, smallint_col, int_col, bigint_col)
9VALUES (255, 32767, 2147483647, 9223372036854775807);

The values are inserted into the table.

MySQL INT Data Types

MySQL has several integer data types:

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:

 1CREATE TABLE mysql_int (
 2tinyint_col TINYINT,
 3smallint_col SMALLINT,
 4mediumint_col MEDIUMINT,
 5int_col INT,
 6bigint_col BIGINT
 7);
 8
 9INSERT INTO mysql_int (tinyint_col, smallint_col, mediumint_col, int_col, bigint_col)
10VALUES (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.

 1CREATE TABLE mysql_int_unsigned (
 2tinyint_col TINYINT UNSIGNED,
 3smallint_col SMALLINT UNSIGNED,
 4mediumint_col MEDIUMINT UNSIGNED,
 5int_col INT UNSIGNED,
 6bigint_col BIGINT UNSIGNED
 7);
 8
 9INSERT INTO mysql_int_unsigned (tinyint_col, smallint_col, mediumint_col, int_col, bigint_col)
10VALUES (255, 65535, 16777215, 4294967295, 18446744073709551616);

Oracle INT Data Types

Here are the INT data types in Oracle SQL:

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:

1CREATE TABLE oracle_int (
2number_col NUMBER
3);
4
5INSERT INTO oracle_int (number_col)
6VALUES (18446744073709551616);

PostgreSQL INT Data Types

PostgreSQL has a few INT data types:

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:

1CREATE TABLE postgresql_int (
2smallint_col SMALLINT,
3integer_col INTEGER,
4bigint_col BIGINT
5);
6
7INSERT INTO sql_server_int (smallint_col,integer_col, bigint_col)
8VALUES (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:

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.