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

this sounds silly question, i've noticed in lot of table designs e-commerce related projects see decimal(19, 4) being used currency.

why 4 on scale? why not 2?

perhaps i'm missing potential calculation issue down road?

first off - receiving incorrect advice other answers. obseve 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 number of underscores underneath title - 39 in all. (i changed every tenth period aid counting.) precisely enough 38 digits (the maximum allowable, , default on 64 bit cpu) plus decimal point on display. although both operands declared decimal(18,2) calculation performed, , reported, in decimal(38,4) datatype. (i running sql 2012 on 64 bit machine - details may vary based on machine architecture , os.)

therefore, clear no precision being lost. on contrary, overflow can occur, not precision loss. direct consequence of calculations on decimal operands being performed integer arithmetic. see artifacts of in intelli-sense when type of intermediate fields of decimal type reported being int instead.

consider example above. 2 operands both of type decimal(18,2) , stored being integers of value 1, scale of 2. when multiplied product still 1, scale evaluated adding scales, create result of integer value 1 , scale 4, value of 0.0001 , of type decimal(18,4), stored integer value 1 , scale 4.

read last paragraph again.

rinse , repeat once more.

in practice, on 64 bit machine , os, stored , carried forward being of type *decimal (38,4) because calculations being done on cpu bits free.

to return question - major currencies of world (that aware of) require 2 decimal places, there handful 4 required, , there financial transactions such currency transactions , bond sales 4 decimal places mandated law. when devising money datatype microsoft appears have opted maximum scale might required rather normal scale required. given how few transactions, , corporations, require precision greater 19 digits seems eminently sensible.

if have:

1. a high expectation of dealing major currencies (which @ current time require 2 digits of scale); and
2. no expectation of dealing transactions mandated law require 4 digits of scale

then safe use type decimal scale 2 (such decimal(19,2) or decimal(18,2) or decimal(38,2)) instead of money. ease of conversions and, given assumptions above, have no cost. typical case these assumptions are met in gl or subledger accounting system tracking transactions penny. however, stock- or bond-trading system not meet these assumptions because 4 digits of scale mandated law in case.

a way distinguish 2 cases whether transactions reported in cents or percents, require 2 digits of scale, or in basis points require 4 digits of scale.

if @ unsure case applies programming circumstance, consult controller or director of finance legal , gaap requirements application. (s)he able give definitive advice.