SQL Server NUMERIC Data Type

SQL Server NUMERIC stores fixed-precision, fixed-scale numbers. DECIMAL is a synonym and can be used interchangeably. Use these types when values need decimal precision, such as monetary amounts; FLOAT and REAL are approximate numeric types.

Syntax

NUMERIC [ ( precision [ , scale ] ) ]

precision is the total number of digits and ranges from 1 to 38; it defaults to 18. scale is the number of digits to the right of the decimal point and ranges from 0 to precision; it defaults to 0. Specify scale only when you also specify precision. See the DECIMAL reference for the synonym.

Usage

NUMERIC is suitable for fixed-precision values such as currency amounts and percentages. Unlike approximate FLOAT and REAL values, it represents decimal values using the selected precision and scale. Arithmetic or conversion can still round when the resulting scale is reduced or overflow when the precision is exceeded; choose precision and scale for the expected values. See Microsoft’s FLOAT and REAL guidance.

Examples

Example 1

In the following example, we create a table called Sales with two fields: Price and Quantity. The Price field uses the NUMERIC data type with a total of 18 digits and 2 decimal places. We insert three records, each with a Price and a Quantity.

CREATE TABLE Sales (
  Price NUMERIC(18, 2),
  Quantity INT
);

INSERT INTO Sales VALUES (10.50, 100);
INSERT INTO Sales VALUES (20.75, 200);
INSERT INTO Sales VALUES (5.25, 50);

When we execute the query, we can see that each record’s Price field retains two decimal places:

SELECT * FROM Sales;

Results:

Price	Quantity
10.50	100
20.75	200
5.25	50

Example 2

In the following example, we use the NUMERIC data type for simple calculations. We create a table called Employee with two fields: Salary and Bonus. The Salary field uses the NUMERIC data type with a total of 18 digits and 2 decimal places. We insert two records, each with a Salary and a Bonus.

CREATE TABLE Employee (
  Salary NUMERIC(18, 2),
  Bonus NUMERIC(18, 2)
);

INSERT INTO Employee VALUES (5000.00, 1000.00);
INSERT INTO Employee VALUES (6000.00, 2000.00);

When we execute the query, we can calculate the total income (Salary + Bonus) for each employee:

SELECT Salary, Bonus, (Salary + Bonus) AS TotalIncome FROM Employee;