Numeric data types in SQL are designed to store numbers in various formats, each with its own characteristics and best use cases. The most common numeric types include INTEGER, SMALLINT, BIGINT, DECIMAL, NUMERIC, and FLOAT. Understanding the differences between these types is essential when designing a database to ensure accuracy, efficiency, and optimal storage.

• INTEGER is used for whole numbers without any fractional or decimal component. It is a good choice for values like counts, IDs, or ages where fractions are not needed;
• SMALLINT is similar to INTEGER but uses less storage and allows a smaller range of values. It is suitable for small numbers, such as ages or quantities that will never be large;
• BIGINT is used for whole numbers that may exceed the range of INTEGER, such as large counters or population figures;
• DECIMAL and NUMERIC are used for numbers that require exact precision, especially for values with a fixed number of decimal places, such as monetary amounts. They allow you to specify both the precision (total number of digits) and scale (number of digits after the decimal point);
• FLOAT is used for approximate numeric values with floating-point precision. It is suitable for scientific or engineering calculations where exact precision is less important than range and performance.

To see how these types are used in practice, consider a table that records employee salaries, ages, and performance ratings, each requiring a different numeric type.

CREATE TABLE employee_stats (
    id INTEGER PRIMARY KEY,
    salary DECIMAL(10,2),
    age SMALLINT,
    rating FLOAT
);

In the table above, the salary column uses the DECIMAL(10,2) type, which can store numbers with up to 10 digits in total, including 2 digits after the decimal point. This makes it ideal for monetary values where precision is critical. The age column uses SMALLINT because age is always a small whole number, so using a smaller type saves storage space. The rating column uses FLOAT to allow fractional values, which are useful for representing performance scores that may not be whole numbers.

Precision and scale are particularly important for types like DECIMAL and NUMERIC. Precision is the total number of digits that can be stored, while scale is the number of digits to the right of the decimal point. For example, DECIMAL(10,2) can store values like 12345678.90. Using types like FLOAT saves space and allows for a wide range of values, but may introduce rounding errors due to how floating-point numbers are represented internally. INTEGER, SMALLINT, and BIGINT types are fixed in size and store only whole numbers, offering fast operations and predictable storage requirements.

Let's look at some example INSERT statements that demonstrate valid and invalid values for each of the numeric columns in the employee_stats table.

-- Valid values
INSERT INTO employee_stats (id, salary, age, rating) VALUES (1, 50000.00, 30, 4.5);
INSERT INTO employee_stats (id, salary, age, rating) VALUES (2, 12345678.90, 65, 3.9);

-- Invalid values
-- Exceeds DECIMAL(10,2) precision
INSERT INTO employee_stats (id, salary, age, rating) VALUES (3, 123456789.00, 40, 4.8);

-- Age too large for SMALLINT
INSERT INTO employee_stats (id, salary, age, rating) VALUES (4, 75000.00, 40000, 4.2);

-- Non-numeric in FLOAT column
INSERT INTO employee_stats (id, salary, age, rating) VALUES (5, 80000.00, 28, 'excellent');

The first two INSERT statements succeed because the values fit the defined precision, scale, and ranges for each column. The third fails because the salary value has more digits than allowed by DECIMAL(10,2). The fourth fails because 40000 is too large for a SMALLINT, which typically stores values up to 32767. The fifth fails because 'excellent' is not a valid floating-point number for the rating column.

Choosing the right numeric type depends on the nature of the data. For monetary values, you need precision and control over decimal places. For scientific or approximate calculations, floating-point may be appropriate. For whole numbers within a limited range, use the smallest integer type that fits your needs to optimize storage.