SQL Server NUMERIC Data Type

The NUMERIC data type is used to store precise numerical values, which can represent fixed precision and higher precision than FLOAT and DOUBLE. In SQL Server, NUMERIC and DECIMAL are the same data type and are used to represent fixed precision numerical values.

Syntax

NUMERIC(p,s)

Where p represents the total number of digits and s represents the number of digits after the decimal point. The value of p ranges from 1 to 38, and the value of s ranges from 0 to p. By default, both p and s have a value of 18.

Usage

The NUMERIC data type is suitable for storing precise numerical values, such as currency amounts and percentages. In scenarios that require high-precision calculations, such as financial and tax calculations, the NUMERIC data type is usually used. Compared to the FLOAT and DOUBLE data types, the NUMERIC data type ensures that precision is not lost due to calculation errors.

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;