Decimal(19,4) or Decimal(19.2) - which should I use?

Question

This sounds like a silly question, but I've noticed that in a lot of table designs for e-commerce related projects I almost always see decimal(19, 4) being used for currency.

Why the 4 on scale? Why not 2?

Perhaps I'm missing a potential calculation issue down the road?

Strictly

when you have a ms-sql database you could use the datatype money. – Pentup

@JoachimSauer - Decimal is more accurate than money, and because I see decimal being used in many e-commerce type examples. – Strictly

The higher precision helps reduce rounding errors - just a guess – Harbird

DECIMAL(19, 4) is a popular choice check this also check here World Currency Formats to decide how many decimal places to use , hope helps. – Yul

Answer 1

First off - you are receiving some incorrect advice from other answers. Obseve the following (64-bit OS on 64-bit architecture):

declare @op1 decimal(18,2) = 0.01
       ,@op2 decimal(18,2) = 0.01;

select result = @op1 * @op2;

result
---------.---------.---------.---------
0.0001

(1 row(s) affected)

Note the number of underscores underneath the title - 39 in all. (I changed every tenth to a period to aid counting.) That is precisely enough for 38 digits (the maximum allowable, and the default on a 64 bit CPU) plus a decimal point on display. Although both operands were declared as decimal(18,2) the calculation was performed, and reported, in decimal(38,4) datatype. (I am running SQL 2012 on a 64 bit machine - some details may vary based on machine architecture and OS.)

Therefore, it is clear that no precision is being lost. On the contrary, only overflow can occur, not precision loss. This is a direct consequence of all calculations on decimal operands being performed as integer arithmetic. You will occasionally see artifacts of this in intelli-sense when the type of intermediate fields of decimal type are reported as being int instead.

Consider the example above. The two operands are both of type decimal(18,2) and are stored as being integers of value 1, with a scale of 2. When multiplied the product is still 1, but the scale is evaluated by adding the scales, to create a result of integer value 1 and scale 4, which is a value of 0.0001 and of type decimal(18,4), stored as an integer with value 1 and scale 4.

Read that last paragraph again.

Rinse and repeat once more.

In practice, on a 64 bit machine and OS, this is actually stored and carried forward as being of type *decimal (38,4) because the calculations are being done on a CPU where the extra bits are free.

To return to your question - All major currencies of the world (that I am aware of) only require 2 decimal places, but there are a handful where 4 are required, and there are financial transactions such as currency transactions and bond sales where 4 decimal places are mandated by law. When devising the money datatype Microsoft appears to have opted for the maximum scale that might be required rather than the normal scale required. Given how few transactions, and corporations, actually require precision greater than 19 digits this seems eminently sensible.

If you have:

1. A high expectation of only dealing with major currencies (which at the current time only require 2 digits of scale); and
2. No expectation of dealing with transactions that are mandated by law to require 4 digits of scale

then you would be safe to use type decimal with scale 2 (such as decimal(19,2) or decimal(18,2) or decimal(38,2)) instead of money. This will ease some of your conversions and, given the assumptions above, have no cost. A typical case where these assumptions are met is in a GL or Subledger accounting system tracking transactions to the penny. However, a stock- or bond-trading system would not meet these assumptions because 4 digits of scale are mandated by law in those case.

A way to distinguish the two cases is whether transactions are reported in cents or percents, which only require 2 digits of scale, or in basis points which require 4 digits of scale.

If you are at all unsure as to which case applies to your programming circumstance, consult your Controller or Director of Finance as to the legal and GAAP requirements for your application. (S)he will be able to give you definitive advice.

Culver

Incredible detail and well thought out. Thank you Pieter. – Strictly

Answer 2

In SQL the 19 is amount of integers, the 4 is amount of decimals.

If you only have 2 decimals and you store maybe a result of some calculations, which results in more than 2 decimals, theres "no way" to store those additional decimals.

Some currencies operates with more than 2 decimals.

Use the data type decimal, not money.

Bund

Isn't the 19 the total amount of integers? And the 4 is taken out of that total number, for usage to the right of the . ? – Strictly

Yes, 19 is amount of integers (includes the decimals). So Decimal(10,3) can maximum have a number of 9999999.999 [1234567.123] – Amalle

Can you think of an example of the need to use 4 on scale instead of 2? Lets say for a table that's storing monthly subscription prices. Looking for an example scenario, possible calculation, or some reason that 4 on scale would be helpful down the road. – Strictly

If you only add and subtract, then 2 digits of precision is enough. When dividing (or calculating percentages) you really need more than 2 digits. For example, consider tax deduction calculations from your payroll. Don't believe me, watch Superman III with Richard Pryor. – Loxodromics

Answer 3

Things like gas prices would use the extra "scale" positions. You've seen gas at $1.959 per gallon, right?

Heth

Answer 4

When use decimal it's up to you how you want to use according to your business requirements.

But when you will use Money data type in sql by default it stores with 4 decimal places.

Shipmate

Answer 5

although the OP's question is about the scale, let's lament on why 19 is a popular precision for decimal on SQL server.

according to this document, this is how much storage a decimal uses:

Precision	Storage bytes
1 - 9	5
10-19	9
20-28	13
29-38	17

so 1 precision uses as much space as 9, and 10 uses as much as 19.

in a real world scenario 9 can easily be too little for money, especially if you opt for a scale of 4, leaving you between -99999.9999 and 99999.9999.

but 19 is plenty for any imaginable cases, that's why SQL Server's money data type uses that.

one can use 28 or 38 to prevent errors at conversions in case some erroneous data hides in the database.

Watermark