The DECIMAL 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 DECIMAL column.
CREATE TABLE DemoTable
(
Id INT IDENTITY,
Employee VARCHAR(100),
Salary DECIMAL(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 DECIMAL.
DECIMAL(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 decimal depends on the precision:
| Precision | Storage (bytes) |
|---|---|
| 1-9 | 5 |
| 10-19 | 9 |
| 20-28 | 13 |
| 29-38 | 17 |
| PRODUCT |
|---|
| Id |
| ProductName |
| SupplierId |
| UnitPrice |
| Package |
| IsDiscontinued |
SELECT ProductName, UnitPrice
FROM Product
| ProductName | UnitPrice |
|---|---|
| Chai | 18.00 |
| Chang | 19.00 |
| Aniseed Syrup | 10.00 |
| Chef Anton's Cajun Seasoning | 22.00 |
| Chef Anton's Gumbo Mix | 21.35 |
 |
|
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 |
