Menu
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?
836
In standard SQL, the syntax DECIMAL( M ) is equivalent to DECIMAL( M ,0) . Similarly, the syntax DECIMAL is equivalent to DECIMAL( M ,0) , where the implementation is permitted to decide the value of M . MySQL supports both of these variant forms of DECIMAL syntax. The default value of M is 10.
In C#, Decimal Struct class is used to represent a decimal floating-point number. The range of decimal numbers is +79,228,162,514,264,337,593,543,950,335 to -79,228,162,514,264,337,593,543,950,335.
Use the CAST() function to convert an integer to a DECIMAL data type. This function takes an expression or a column name as the argument, followed by the keyword AS and the new data type. In our example, we converted an integer (12) to a decimal value (12.00).
To store numbers that have fixed precision and scale, you use the DECIMAL data type. In this syntax: p is the precision which is the maximum total number of decimal digits that will be stored, both to the left and to the right of the decimal point.
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.
113
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
