What's the best data type in SQL to represent Decimal in .NET?
We want to store decimal numbers with up to 9 decimal place precision and want to avoid rounding errors etc on the front end.
Reading about data types, it appears using Decimal in .NET is the best option because you will not get rounding errors, although it is a bit slower than a Double.
We want to carry this through down to the DB and want minimum conversion issues when moving data through the layers. Any suggestions?
I would use the decimal type in SQL Server
Take a look at SQL Server Data Type Mappings (ADO.NET)
If you're using SQL Server, this table might help with data type mappings.
So we did some testing on SQL Server. It looks like the sql type decimal cannot completely store any .net decimal.
SQL Server can store a number up to 38 decimal digits long. That's the total of the number of digits to the left and the right of the decimal place. You set a 'Scale', which tells SQL server how many decimal digits to reserve for the number to the right of the decimal place. If you set a scale then that takes away from the number of digits to the left of the decimal point. (Precision - Scale = number of decimal digits to the left of the decimal place)
.NET can represent up to 28 digits to the right of the decimal point and 29 to the left. That would require a Precision of 57 in SQL Server but the max available is 38.
So if you want to get as much precision as possible and your number are small enough then you could do this:
decimal(38, 28)
That would leave you with 10 digits to the left and 28 digits to the right. So any number larger than 9999999999 couldn't be represented but you wouldn't loose precision when doing currency type transactions.
On the other hand if your numbers are very large you could store them with this declaration:
decimal(38, 9)
This would let you store the largest number that .net Decimal can store, which is 29 digits long. It would leave you with just 8 decimal digits of precision.
If none of this sounds appealing then you can just store them as varchar. That would be allow you to save any .net decimal but it wouldn't let you perform any calculations on them in SQL.