Menu
I am having to interface some C# code with SQL Server and I want to have exactly as much precision when storing a value in the database as I do with my C# code. I use one of .NET's decimal type for a value. What datatype/precision would I use for this value in SQL Server?
I am aware that the SQL Server decimal type is the type that most likely fits my needs for this. My question though is what scale and precision do I use so that it matches .NET's decimal type?
228
C# decimal precision The decimal type is a 128-bit floating point data type; it can have up to 28-29 significant digits.
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.
Precision is the number of digits in a number. Scale is the number of digits to the right of the decimal point in a number. For example, the number 123.45 has a precision of 5 and a scale of 2. In SQL Server, the default maximum precision of numeric and decimal data types is 38.
Fluent nHibernate maps System.Decimal as
decimal(19, 5) You can use the SQL Server decimal type (documentation here). That allows you to specify a precision and scale for the numbers in the database, which you can set to be the same as in your C# code.
Your question, however, seems to be about mapping the .NET decimal type to the SQL Server decimal type. And unfortunately, that's not straightforward. The SQL Server decimal, at maximum precision, covers the range from -1038+1 to 1038-1. In .NET, however, it can cover anywhere from ±10−28 to ±7.9×1028, depending on the number.
The reason is that, internally, .NET treats decimals very similar to floats—it stores it as a pair of numbers, a mantissa and an exponent. If you have an especially large mantissa, you're going to lose some of the precision. Essentially, .NET allows you to mix numbers which have both a high and a low scale; SQL Server requires you to choose it for a given column ahead of time.
In short, you're going to need to decide what the valid range of decimal values are, for your purposes, and ensure both your program and the database can handle it. You need to do that anyway for your program (if you're mixing values like 10−28 with 7.9×1028, you can't rely on the decimal type anyway).
31
This chart may help you.
Maximum precision for numerics and decimals in SQL Server is 38 (see here).
34
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
