SQL SMALLINT

What is SQL SMALLINT?

SMALLINT is a fixed-precision, 2-byte signed integer data type that stores whole numbers within a limited range.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL SMALLINT:

SQL SMALLINT Full Explanation

SMALLINT is a numeric data type defined by the SQL standard to hold small whole numbers. It occupies 2 bytes of storage and typically supports a signed range from ‑32,768 to 32,767. Because it uses less space than INT or BIGINT, it is ideal for columns whose values are known to remain within that range, such as age, month number, status codes, or small lookup table identifiers.Behavior- Arithmetic involving SMALLINT follows normal integer math; overflows raise errors or wrap depending on the dialect.- Comparisons, indexing, and joins on SMALLINT columns are generally faster and more memory-efficient than on larger integer types.- In MySQL and MariaDB, SMALLINT can be UNSIGNED (0 to 65,535) and can specify a display width, but display width has no impact starting with MySQL 8.0.- In SQL Server, SMALLINT is always signed and ranges from ‑32,768 to 32,767.- In PostgreSQL, SMALLINT is an alias for INT2 and behaves identically.Caveats- Choosing SMALLINT for a column that later exceeds its range requires an online type change or table rebuild, which can be disruptive.- Some ORMs default to INT, so explicit type mapping may be needed.- Arithmetic promotion rules vary: some engines upcast the result of SMALLINT math to INT to avoid overflow.

SQL SMALLINT Syntax

-- Column definition in a CREATE TABLE
column_name SMALLINT;

-- With optional dialect-specific attributes (MySQL example)
column_name SMALLINT UNSIGNED NOT NULL;

SQL SMALLINT Parameters

Example Queries Using SQL SMALLINT

-- 1. Create a table using SMALLINT for a status code
CREATE TABLE orders (
    order_id     INT PRIMARY KEY,
    status_code  SMALLINT NOT NULL,
    quantity     SMALLINT
);

-- 2. Insert valid data within range
INSERT INTO orders (order_id, status_code, quantity)
VALUES (1, 10, 250);

-- 3. Attempt to insert a value out of range (will error)
INSERT INTO orders (order_id, status_code, quantity)
VALUES (2, 40000, 5);

-- 4. Change a column from INT to SMALLINT to save space (PostgreSQL)
ALTER TABLE orders
    ALTER COLUMN quantity TYPE SMALLINT;

Expected Output Using SQL SMALLINT

Table created with status_code and quantity columns stored in 2 bytes each.
Row inserted successfully because values are within the SMALLINT range.
Database raises an out-of-range or numeric value error.
Column storage shrinks; future inserts must respect the SMALLINT limits.

Use Cases with SQL SMALLINT

Modeling enumerations or status codes that never exceed 32,767
Storing counts, ages, or small quantities where space efficiency matters
Creating foreign keys to small dimension tables
Optimizing large fact tables by using the smallest adequate integer type

Common Mistakes with SQL SMALLINT

Underestimating future growth and hitting the 32,767 limit
Assuming SMALLINT is automatically unsigned in all databases
Forgetting that arithmetic on SMALLINT can overflow silently in some engines
Relying on MySQL display width for formatting in versions where it is ignored

First Introduced In

SQL-86 (First SQL standard)

Frequently Asked Questions

What is the exact storage size of SMALLINT?

SMALLINT occupies 2 bytes (16 bits) of storage regardless of the database engine.

How do I make a SMALLINT column unsigned?

In MySQL or MariaDB append the keyword UNSIGNED: `age SMALLINT UNSIGNED`. Other databases do not support an unsigned modifier for SMALLINT.

Will arithmetic on SMALLINT overflow?

Yes. Adding or multiplying values that exceed the SMALLINT range can overflow. Many databases promote the result to INT, but some may raise an error or wrap. Test critical calculations.

Can I index a SMALLINT column?

Absolutely. SMALLINT columns are often indexed because their small size speeds up B-tree traversals and reduces index storage.