Menu

SQL Server DECIMAL Data Type

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

Syntax

The syntax for the DECIMAL data type is:

DECIMAL [ ( 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 NUMERIC reference for the synonym.

Usage

The DECIMAL data type is typically used in scenarios that require high precision calculations, such as:

  • Financial calculations: In financial reports, it is necessary to accurately calculate various income, expenditure, and tax values, as well as post-tax profits and other indicators.
  • Currency calculations: Precise exchange rates are required when converting currencies.
  • Scientific calculations: High-precision numerical representations are required for certain scientific calculations, such as calculating pi.

Example

Here is an example of using the DECIMAL data type to store exam scores for students:

CREATE TABLE ExamScores (
    StudentID INT PRIMARY KEY,
    Score DECIMAL(4, 2) NOT NULL
);

INSERT INTO ExamScores (StudentID, Score)
VALUES (1, 78.50),
       (2, 93.75),
       (3, 87.00);

SELECT * FROM ExamScores;

In the above example, we created a table named ExamScores that contains two fields, StudentID and Score. The Score field uses the DECIMAL data type with a total of 4 digits and 2 decimal places. We inserted three records, each containing a student ID and an exam score. Finally, we used the SELECT statement to query the entire table.

Here is another example of using the DECIMAL data type to store revenue for a company:

CREATE TABLE Sales (
    Month INT NOT NULL,
    Year INT NOT NULL,
    Revenue DECIMAL(18, 2) NOT NULL
);

INSERT INTO Sales (Month, Year, Revenue)
VALUES (1, 2022, 124567.89),
       (2, 2022, 165432.10),
       (3, 2022, 198765.43);

SELECT * FROM Sales;

In this example, we created a table named Sales that contains three fields, Month, Year, and Revenue. The Revenue field uses the DECIMAL data type with a total of 18 digits and 2 decimal places. We inserted three records, each containing a month, a year, and the revenue for that month.

Conclusion

DECIMAL and NUMERIC are interchangeable fixed-precision types in SQL Server. Choose precision and scale for the values you need to store; conversions to a lower precision or scale can round values, and values outside the chosen range can overflow. For approximate numeric values, see FLOAT or REAL.

Advertisement