SQL Server中的数字，浮点数和小数之间的差异

本文翻译自：Difference between numeric, float and decimal in SQL Server

I searched in Google and also visited decimal and numeric and SQL Server Helper to glean the difference between numeric, float and decimal datatypes and also to find out which one should be used in which situation. 我在Google中进行了搜索，还访问了十进制和数字以及SQL Server Helper，以收集数字，浮点和十进制数据类型之间的差异，并找出在哪种情况下应使用哪种。

For any kind of financial transaction (eg for salary field), which one is prefered and why? 对于任何形式的金融交易（例如，薪水领域），首选哪种交易，为什么？

---

#1楼

参考：https://stackoom.com/question/4QnT/SQL-Server中的数字-浮点数和小数之间的差异

---

#2楼

Decimal has a fixed precision while float has variable precision. 小数具有固定的精度，而浮点具有可变的精度。

EDIT (failed to read entire question): Float(53) (aka real) is a double-precision (32-bit) floating point number in SQL Server. 编辑（无法阅读整个问题）：Float（53）（又名实数）是SQL Server中的双精度（32位）浮点数。 Regular Float is a single-precision floating point number. 常规浮点数是单精度浮点数。 Double is a good combination of precision and simplicty for a lot of calculations. 对于许多计算而言，Double是精度和简单性的良好组合。 You can create a very high precision number with decimal -- up to 136-bit -- but you also have to be careful that you define your precision and scale correctly so that it can contain all your intermediate calculations to the necessary number of digits. 您可以使用十进制创建一个非常高精度的数字（最大136位），但是您还必须注意正确定义精度和小数位数，以便它可以将所有中间计算包含到所需的位数。

---

#3楼

Not a complete answer, but a useful link: 不是完整的答案，而是有用的链接：

"I frequently do calculations against decimal values. In some cases casting decimal values to float ASAP, prior to any calculations, yields better accuracy. " “我经常根据十进制值进行计算。在某些情况下，在进行任何计算之前，将十进制值强制转换为ASAP浮点数会产生更好的精度。”

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2008/12/20/for-better-precision-cast-decimals-before-calculations.aspx http://sqlblog.com/blogs/alexander_kuznetsov/archive/2008/12/20/for-better-precision-cast-decimals-before-calculations.aspx

---

#4楼

Guidelines from MSDN: Using decimal, float, and real Data MSDN指南： 使用十进制，浮点和实数据

The default maximum precision of numeric and decimal data types is 38. In Transact-SQL, numeric is functionally equivalent to the decimal data type. 数字和十进制数据类型的默认最大精度为38。在Transact-SQL中，数字在功能上等效于十进制数据类型。 Use the decimal data type to store numbers with decimals when the data values must be stored exactly as specified. 当必须完全按照指定的方式存储数据值时，请使用十进制数据类型存储带小数的数字。

The behavior of float and real follows the IEEE 754 specification on approximate numeric data types. float和real的行为遵循关于近似数字数据类型的IEEE 754规范。 Because of the approximate nature of the float and real data types, do not use these data types when exact numeric behavior is required, such as in financial applications, in operations involving rounding, or in equality checks. 由于float和real数据类型的近似性质，因此在需要精确的数字行为时（例如，在金融应用程序中，涉及舍入的操作中或在相等性检查中），请勿使用这些数据类型。 Instead, use the integer, decimal, money, or smallmoney data types. 而是使用整数，十进制，货币或小货币数据类型。 Avoid using float or real columns in WHERE clause search conditions, especially the = and <> operators. 避免在WHERE子句搜索条件中使用浮点或实数列，尤其是=和<>运算符。 It is best to limit float and real columns to > or < comparisons. 最好将浮点数和实数列限制为>或<比较。

---

#5楼

They Differ in Data Type Precedence 他们的数据类型优先级不同

Decimal and Numeric are the same functionally but there is still data type precedence , which can be crucial in some cases. 小数和数值在功能上相同，但是仍然存在数据类型优先级 ，这在某些情况下可能至关重要。

SELECT SQL_VARIANT_PROPERTY(CAST(1 AS NUMERIC) + CAST(1 AS DECIMAL),'basetype')

The resulting data type is numeric because it takes data type precedence . 结果数据类型为数字，因为它优先于数据类型 。

Exhaustive list of data types by precedence: 按优先级列出数据类型的详尽列表：

Reference link 参考链接

---

#6楼

Float is Approximate-number data type, which means that not all values in the data type range can be represented exactly. 浮点数是“近似数字”数据类型，这意味着不能准确表示该数据类型范围内的所有值。

Decimal/Numeric is Fixed-Precision data type, which means that all the values in the data type range can be represented exactly with precision and scale. 十进制/数字是固定精度数据类型，这意味着可以使用精度和小数位数精确表示数据类型范围内的所有值。 You can use decimal for money saving. 您可以使用十进制来省钱。

Converting from Decimal or Numeric to float can cause some loss of precision. 从十进制或数字转换为浮点数可能会导致精度损失。 For the Decimal or Numeric data types, SQL Server considers each specific combination of precision and scale as a different data type. 对于十进制或数字数据类型，SQL Server将精度和小数位的每种特定组合视为不同的数据类型。 DECIMAL(2,2) and DECIMAL(2,4) are different data types. DECIMAL（2,2）和DECIMAL（2,4）是不同的数据类型。 This means that 11.22 and 11.2222 are different types though this is not the case for float. 这意味着11.22和11.2222是不同的类型，尽管float并非如此。 For FLOAT(6) 11.22 and 11.2222 are same data types. 对于FLOAT（6），11.22和11.2222是相同的数据类型。

You can also use money data type for saving money. 您也可以使用money数据类型来省钱。 This is native data type with 4 digit precision for money. 这是本机数据类型，具有4位数的精度。 Most experts prefers this data type for saving money. 大多数专家都喜欢使用这种数据类型来省钱。

Reference 1 2 3 参考1 2 3