FB

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.

3 thoughts on “SQL INT Data Types in Different Databases”

  1. David Bruce Fowler

    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

  2. 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

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Table of Contents