.NET十进制到SQL Server十进制的良好映射是什么？ [英] What is a good mapping of .NET decimal to SQL Server decimal?

问题描述

我必须将一些C＃代码与SQL Server接口，并且我希望在数据库中存储值时具有与使用C＃代码一样精确的精度。我使用.NET的十进制类型之一作为值。我将在SQL Server中为该值使用什么数据类型/精度？

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?

我知道SQL Server 十进制类型最有可能满足我的需求。我的问题是，我应该使用什么规模和精度，以便它与.NET的十进制类型匹配？

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?

推荐答案

您可以使用SQL Server 十进制类型（此处的文档）。这样就可以为数据库中的数字指定精度和小数位数，可以将其设置为与C＃代码中的数字相同。

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.

您的问题是，似乎与将.NET十进制类型映射到SQL Server十进制类型有关。不幸的是，这并不简单。 SQL Server十进制以最高精度覆盖从-10 ³⁸ +1到10 ³⁸ -1的范围。但是，在.NET中，它可以覆盖±10 ⁻²⁸ 到±7.9×10 ²⁸ 的任意范围，具体取决于数字。

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 -10³⁸+1 to 10³⁸-1. In .NET, however, it can cover anywhere from ±10⁻²⁸ to ±7.9×10²⁸, depending on the number.

原因是，.NET在内部将小数与浮点数非常相似-将其存储为一对数字，一个尾数和一个指数。如果尾数特别大，那么您将失去一些精度。本质上，.NET允许您混合使用具有高低比例的数字。 SQL Server要求您提前为给定的列选择它。

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.

简而言之，您将需要确定出于您的目的，十进制值的有效范围应确保程序和数据库都可以处理。无论如何，您都需要为程序执行此操作（如果您将10 ⁻²⁸ 之类的值与7.9×10 ²⁸ 相混合，则无论如何都不能依赖小数类型）。

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⁻²⁸ with 7.9×10²⁸, you can't rely on the decimal type anyway).

这篇关于.NET十进制到SQL Server十进制的良好映射是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！