什么是SQL中的最佳数据类型重新在.NET present十进制？

What's the best data type in SQL to represent Decimal in .NET?

我们想要存储的十进制数最多9小数位precision，并希望避免舍入误差等的前端。

We want to store decimal numbers with up to 9 decimal place precision and want to avoid rounding errors etc on the front end.

阅读有关数据类型，它出现使用十进制在.NET中是最佳的选择，因为你不会得到舍入误差，虽然它比双慢了一点。

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.

我们希望通过开展这一下降到DB和希望最低的转换问题经过层层移动数据时。有什么建议？

We want to carry this through down to the DB and want minimum conversion issues when moving data through the layers. Any suggestions?

推荐答案

所以我们做SQL Server的一些测试。它看起来像SQL类型十进制不能完全存储任何.NET小数。

So we did some testing on SQL Server. It looks like the sql type decimal cannot completely store any .net decimal.

SQL Server可以存储数达 38位小数长。这是数字的左边数和小数位的右侧的总和。您可以设置一个'秤'，它告诉SQL服务器多少个十进制数字预留的号到小数点后一位的右侧。如果设置了刻度那么需要远离的位数的小数点的左边。 （precision - 规模=十进制数字到小数点左边的数字）

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可以重新present多达28位小数点和29向左右侧。这就需要在SQL Server $ P $ 57 pcision但可以最大为38。

.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.

因此​​，如果你希望得到尽可能多的 precision 的越好，你的电话号码是足够小，那么你可以这样做：

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)

这将让你与10位的左侧和28位的权利。因此，任何数量超过9999999999较大无法再presented，但你不会松动precision做币种交易时。

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)

这将让您存储数量最多的是.NET十进制可以存储，这是29位长。这将让你只用8位数字的precision数字。

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.

如果没有这娓娓动听那么你可以将它们存储为 VARCHAR 。这将是让您保存任何.NET小数，但它不会让你在SQL对其执行任何计算。

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.