What Precision and Scale Should I Use for Storing Money in a Decimal Column?

Storing Money in a Decimal Column: Precision and Scale Considerations

Database designers often face the dilemma of choosing the appropriate precision and scale for decimal columns used to store money values. This article explores the considerations involved and provides insights into the optimal values for different scenarios.

Precision and Scale for Efficient Storage

While fixed-width char columns may offer efficiency benefits, decimal columns also have their advantages. However, it's not necessarily true that the lower the precision and scale, the more efficient the storage.

Recommended Precision and Scale

For general use, DECIMAL(19,4) is a commonly accepted choice. This stems from the historical Currency data type in VBA/Access/Jet and the need to support bankers' rounding, which allows for custom rounding algorithms.

Overkill or Not Enough?

DECIMAL(24,8) may seem excessive for most currency applications, as most currencies are quoted with four or five decimal places. In scenarios where extended precision is required, it's advisable to consider floating point types instead, as they can handle a wider range of values.

Accounting Rules and Considerations

To ensure accuracy and compliance, it's crucial to consult with domain experts and accounting standards. Different jurisdictions may have specific regulations regarding the rounding and precision of monetary values. For example, EU intra-state transfers may dictate the use of DECIMAL(p,6) for storage.

Avoid SQL Server's MONEY Data Type

The MONEY data type in SQL Server has known issues with accuracy and rounding. Instead, using a DECIMAL data type is recommended for optimal precision and portability.

Historical Background on Bankers' Rounding

Bankers' rounding is widely used in computing because it is enshrined in IEEE standards and preferred by mathematicians. It is often referred to as "the computer's rule," with origins in human computational practices.

The above is the detailed content of What Precision and Scale Should I Use for Storing Money in a Decimal Column?. For more information, please follow other related articles on the PHP Chinese website!