SQL TINYINT

What is SQL TINYINT?

TINYINT stores very small whole numbers in a single byte, typically ranging from -128 to 127 (signed) or 0 to 255 (unsigned).

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 TINYINT:

SQL TINYINT Full Explanation

TINYINT is the smallest standard integer data type available in several SQL dialects. It occupies exactly 1 byte (8 bits) of storage and is ideal for columns that will never exceed a few hundred distinct values. The signed range is -128 to 127, while the unsigned range is 0 to 255. Behavior differs slightly by database:- MySQL and MariaDB support both signed (default) and UNSIGNED variants, optional ZEROFILL, and formerly allowed a display width (now deprecated).- SQL Server supports only an unsigned range (0 to 255) and treats TINYINT as always positive.- SQLite applies dynamic typing; declaring a column TINYINT gives it INTEGER affinity, still stored efficiently as 1 byte when values fit.- PostgreSQL and Oracle do not have a native TINYINT; use SMALLINT or NUMBER(3) instead, or create a domain.Casting or arithmetic operations will silently promote TINYINT to a larger integer type when needed. Overflow on insert or update raises an error in strict SQL modes but may clip or wrap in lenient modes. Always verify range before assignment.

SQL TINYINT Syntax

-- Column definition
column_name TINYINT [UNSIGNED] [ZEROFILL] [NOT NULL] [DEFAULT <value>];

-- CAST usage
SELECT CAST(42 AS TINYINT) AS tiny_val;

SQL TINYINT Parameters

UNSIGNED (keyword) - Makes the range 0 to 255 instead of -128 to 127 (MySQL/MariaDB).
ZEROFILL (keyword) - Left-pads the stored value with zeros when selected (MySQL/MariaDB; implies UNSIGNED).
Display width (integer) - Deprecated MySQL syntax TINYINT(m) that specified display width; ignored in 8.0+.

Example Queries Using SQL TINYINT

-- 1. Create a lookup table with a TINYINT key
CREATE TABLE status_codes (
  id TINYINT UNSIGNED PRIMARY KEY,
  description VARCHAR(50)
);
INSERT INTO status_codes (id, description) VALUES
  (0, 'Inactive'),
  (1, 'Active'),
  (2, 'Pending');

-- 2. Enforce range on insert (will fail)
INSERT INTO status_codes (id, description) VALUES (300, 'Out of range');

-- 3. Use TINYINT in a larger query
SELECT u.user_id,
       s.description AS status
FROM users u
JOIN status_codes s ON u.status_id = s.id
WHERE s.id = 1;

Expected Output Using SQL TINYINT

Table created and three rows inserted successfully.
Insert fails with "Out of range" error because 300 exceeds 255.
Query returns only users whose status_id equals 1 with description 'Active'.

Use Cases with SQL TINYINT

Storing boolean or flag values without using BIT.
Enumerations such as status codes, rating levels, or small categorical data.
Memory-constrained systems where every byte counts.
High-volume fact tables where column size directly impacts I/O and cache efficiency.

Common Mistakes with SQL TINYINT

Assuming negative values are allowed in SQL Server (they are not).
Forgetting to mark the column UNSIGNED when negative numbers are impossible, leading to wasted range.
Using display width TINYINT(1) in MySQL and expecting it to behave like BOOLEAN; it only affects formatting.
Overflowing the 0-255 or -128-127 limits, causing insert or update errors.

First Introduced In

Microsoft SQL Server 6.0 (1995)

Frequently Asked Questions

Is TINYINT always unsigned in every database?

No. SQL Server enforces an unsigned 0-255 range, while MySQL defaults to signed but lets you add the UNSIGNED attribute.

Why does MySQL show TINYINT(1) for BOOLEAN columns?

BOOLEAN is a synonym for TINYINT(1) in MySQL. The (1) is deprecated display width metadata and does not constrain the value to 0 or 1.

Can I store NULL in a TINYINT column?

Yes, unless you declare the column NOT NULL. NULL consumes no space in fixed-length rows and signals unknown or missing data.

How do I simulate TINYINT in PostgreSQL?

Use SMALLINT and optionally add a CHECK constraint: `age SMALLINT CHECK (age BETWEEN 0 AND 255)`.