How does SQL Server know what precision to use for money

Question

How does SQL Server know to retrieve these values this way?

Key         someMoney
----------- ---------------------
1           5.00
2           5.002
3           5.0001

Basically, I'm wondering how to know how many decimal places there are without much of a performance hit.

I want to get

Key         someMoney             places
----------- --------------------- ----------
1           5.00                  2
2           5.002                 3
3           5.0001                4

Answer 1

Money has 4 decimal places....it's a fixed-point data type.

• http://msdn.microsoft.com/en-us/library/ms179882.aspx
• Is SQL Server 'MONEY' data type a decimal floating point or binary floating point?

Answer 2

This produces the correct results, but I'm not sure if it performs well enough for you and I haven't tried it with data other than the examples you listed:

;
with money_cte ([Key], [someMoney])
as
(
    select 1, cast(5.00 as money)
    union
    select 2, cast(5.002 as money)
    union
    select 3, cast(5.0001 as money)
)

select [Key], [someMoney], abs(floor(log10([someMoney] - round([someMoney], 0, 1)))) as places
from money_cte 
where [someMoney] - round([someMoney], 0, 1) <> 0

union

select [Key], [someMoney], 2 as places
from money_cte 
where [someMoney] - round([someMoney], 0, 1) = 0