Exact Numeric Data Types

These data types store exact numeric values. They are used for calculations where precision is critical, such as financial applications.

Integer Types

Integer types store whole numbers. The range of values that can be stored depends on the specific integer type.

Data Type	Storage Size	Range	Description
`TINYINT`	1 byte	0 to 255	Unsigned integer.
`SMALLINT`	2 bytes	-32,768 to 32,767	Signed integer.
`INT`	4 bytes	-2,147,483,648 to 2,147,483,647	Signed integer. This is the most common integer type.
`BIGINT`	8 bytes	-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807	Signed integer for very large numbers.

Fixed-Point Numeric Types

Fixed-point types store numbers with a fixed number of decimal places. This ensures exact representation of decimal values.

Data Type	Storage Size	Precision	Scale	Range	Description
`DECIMAL(p, s)`	Variable (up to 17-34 bytes)	1-38	0-38	Depends on precision and scale.	Fixed-precision and scale number. `p` is total digits, `s` is digits after decimal.
`NUMERIC(p, s)`	Variable (up to 17-34 bytes)	1-38	0-38	Depends on precision and scale.	Synonym for `DECIMAL`.
`MONEY`	8 bytes	19, 4	4	-922,337,203,685,477.5808 to +922,337,203,685,477.5807	Currency values. Precision is fixed.
`SMALLMONEY`	4 bytes	10, 4	4	-214,748.3648 to +214,748.3647	Smaller currency values. Precision is fixed.

Key Considerations for Exact Numeric Types

Note on Precision and Scale

When using DECIMAL or NUMERIC, you must specify the precision (total number of digits) and scale (number of digits to the right of the decimal point). For example, DECIMAL(10, 2) can store numbers like 12345678.90.

Tip for Financial Calculations

For financial calculations where exact values are paramount, DECIMAL or NUMERIC are strongly recommended over floating-point types like FLOAT or REAL due to their precise representation of decimal fractions.

Important: Data Overflow

Be mindful of the storage size and range of each numeric type. Storing a value outside the supported range will result in a data overflow error.

Example Usage

Creating a Table with Exact Numeric Types


CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100),
    Price DECIMAL(10, 2), -- Stores prices up to 99,999,999.99
    StockQuantity SMALLINT,
    LargeProductID BIGINT
);

Inserting Data


INSERT INTO Products (ProductID, ProductName, Price, StockQuantity, LargeProductID)
VALUES (1, 'Laptop', 1200.50, 50, 10000000000);

INSERT INTO Products (ProductID, ProductName, Price, StockQuantity, LargeProductID)
VALUES (2, 'Keyboard', 75.99, 200, 10000000001);

Querying Data


SELECT ProductID, ProductName, Price
FROM Products
WHERE Price > 1000;

The exact numeric types provide robust solutions for scenarios requiring precise numerical representations. Choose the type that best suits your data's range and precision requirements.