Decimal

In Microsoft SQL Server (MSSQL), the DECIMAL data type is used to store exact numeric values with a fixed precision and scale. It is commonly used for financial and other calculations where precision

What is DECIMAL?

• DECIMAL(p, s):

  • p (precision) is the total number of digits that can be stored, both to the left and right of the decimal point.

  • s (scale) is the number of digits that can be stored to the right of the decimal point.

Examples:

1. DECIMAL(18, 0):

• Precision (p): 18 digits in total.

• Scale (s): 0 digits after the decimal point.

• How it works: This means the column can store up to 18 digits, but none of them can be fractional. It is essentially an integer with a large range.

• Example values: 123456789012345678, -987654321098765432.

2. DECIMAL(20, 2):

• Precision (p): 20 digits in total.

• Scale (s): 2 digits after the decimal point.

• How it works: This means the column can store up to 20 digits, with 2 of those digits reserved for the fractional part.

• Example values: 123456789012345678.99, -987654321098765432.10.

Key Points:

• Precision (p): The total number of digits that can be stored, including both the integer and fractional parts.

• Scale (s): The number of digits that can be stored after the decimal point.

• Storage Size: The storage size for DECIMAL depends on the precision:

  • 1-9 digits: 5 bytes

  • 10-19 digits: 9 bytes

  • 20-28 digits: 13 bytes

  • 29-38 digits: 17 bytes

Max Length and Practical Use:

• DECIMAL(20, 2) is often used for financial data where you need to store large numbers with two decimal places (e.g., currency values).

• DECIMAL(18, 0) is useful for large integer values where fractional parts are not needed.

Example Table Definition:

CREATE TABLE FinancialData (
    TransactionID INT PRIMARY KEY,
    Amount DECIMAL(20, 2),  -- Stores amounts with up to 20 digits, 2 of which are decimals
    LargeInteger DECIMAL(18, 0)  -- Stores large integers with up to 18 digits
);

Summary:

• DECIMAL(18, 0): Stores large integers with up to 18 digits.

• DECIMAL(20, 2): Stores numbers with up to 20 digits, 2 of which are decimals, commonly used for financial data.