Currency or Double preferred?

I'm wondering whether it's better to choose Currency or Double for measure datatypes if the aggregated values will fit inside either datatype. Thoughts? Which would be smaller in terms of storage? If the number gets real big will either Currency or Double lose precision? Are rounding errors more prevalent with one rather than the other?

I have read the following pages but can't seem to make heads or tails of it in terms of best practices:

http://msdn2.microsoft.com/en-us/library/ms129408.aspx

http://msdn2.microsoft.com/en-us/library/system.data.oledb.oledbtype.aspx

http://msdn2.microsoft.com/en-us/library/678hzkk9(VS.80).aspx

http://msdn2.microsoft.com/en-us/library/364x0z75(VS.80).aspx

There is no Double in SQL Server so you have to use either Currency or Decimal or you may have to do conversion before sending the value to SQL Server. Try the link below for the SQL Server Types, ADO.NET types and .NET types. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms131092.aspx

|||

Caddre, thanks for the reply. That was a good link if you're doing SQLCLR stuff.

The Double SSAS datatype is equivalent to the float datatype in SQL Server. The Currency SSAS datatype is equivalent to the money datatype in SQL Server. Regardless, I'm not interested in SQL Server datatypes, just Analysis Services.

The question still remains, which datatype do you choose in SSAS if either will work: Currency or Double?

|||

Float is not a data type for persistence in any layer of SQL Server because of known precision issues in all programming languages. And my reply was related to your posted links which are clr related.

|||

Hi Furmangg,

If you have to store financial information in your cube and your database stores it as money(currency) you should definitely use currency data type for your mesures. Operations with the currency data type take less CPU power (no FPU calculations) and moreover will be better comressed by the storage engine.

Also if your information could be stored in currency data type (from -922,337,203,685,477.5808 to 922,337,203,685,477.5807) you schould take this type for your measures.

Best regards,

Vladimir Chtepa

|||

Thanks Vladimir. That's the answer I was looking for. I appreciate it.