The NUMERIC
data type is an exact number with a fixed precision and scale.
Precision is an integer representing the total number of digits allowed in a column.
Scale is also an integer value that represents the number of decimal places.
A table with a NUMERIC
column.
CREATE TABLE DemoTable
(
Id INT IDENTITY,
Employee VARCHAR(100),
Salary NUMERIC(10,2)
);
GO
INSERT INTO DemoTable VALUES ('Harold Smith', 2350.2);
INSERT INTO DemoTable VALUES ('Robert Johnson', 1874.667);
INSERT INTO DemoTable VALUES ('Janice Lopez', 869.352);
INSERT INTO DemoTable VALUES ('Kelly Wilson', 2500.50);
INSERT INTO DemoTable VALUES ('Grace Taylor', 900);
GO
SELECT * FROM DemoTable;
GO
DROP TABLE DemoTable;
GO
Id | Employee | Salary |
---|---|---|
1 | Harold Smith | 2350.20 |
2 | Robert Johnson | 1874.67 |
3 | Janice Lopez | 869.35 |
4 | Kelly Wilson | 2500.50 |
5 | Grace Taylor | 900.00 |
Employee salary can hold up to 10 digits and 2 decimal places.
If the value is more than 2 decimal places, it will be rounded.
If the value is less than 2 decimal places, a zero is added to complete the 2 digits.
Syntax of NUMERIC.
NUMERIC(precision, scale)
precision
-- the maximum number of digits the decimal may store. Precision includes both left and right side of decimal point. It accepts values from 1 to 38. The default is 18.
scale
-- optional, specifies the number of digits after the decimal point. Scale must be between 0 up to the same value as the precision.
The storage used by numeric depends on the precision:
Precision | Storage (bytes) |
---|---|
1-9 | 5 |
10-19 | 9 |
20-28 | 13 |
29-38 | 17 |
CREATE TABLE DemoTable
(
MyDecimal DECIMAL(10,3),
MyNumeric NUMERIC(10,3)
);
GO
INSERT INTO DemoTable VALUES (1899.982, 1899.982);
GO
SELECT * FROM DemoTable;
GO
DROP TABLE DemoTable;
GO
MyDecimal | MyNumeric |
---|---|
1899.982 | 1899.982 |