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.

我在谷歌中搜索，还访问了decimal和numeric和SQL Server Helper，以收集数值、浮点数和十进制数据类型之间的区别，并找出在哪种情况下应该使用哪个数据类型。

For any kind of financial transaction (e.g. for salary field), which one is prefered and why?

对于任何类型的金融交易(如薪酬领域)，你更喜欢哪一种，为什么?

6 个解决方案

#1

use the float or real data types only if the precision provided by decimal (up to 38 digits) is insufficient

只有当十进制(最多38位)提供的精度不足时，才使用浮点数或真实数据类型

• Approximate numeric data types do not store the exact values specified for many numbers; they store an extremely close approximation of the value.(Technet)

  近似数字数据类型不存储为许多数字指定的精确值;他们储存了非常接近的价值。

• Avoid using float or real columns in WHERE clause search conditions, especially the = and <> operators (Technet)

  在WHERE子句搜索条件中避免使用float或real列，特别是=和<>操作符(Technet)

so generally because the precision provided by decimal is [10E38 ~ 38 digits] if your number can fit in it, and smaller storage space (and maybe speed) of Float is not important and dealing with abnormal behaviors and issues of approximate numeric types are not acceptable, use Decimal generally.

因此，一般来说，由于十进制提供的精度是[10E38 ~ 38位]，如果您的数字可以容纳的话，而且浮点数的存储空间(可能还有速度)较小并不重要，处理异常行为和近似数值类型的问题是不可接受的，一般使用十进制。

more useful information

更多有用的信息

• numeric = decimal (5 to 17 bytes) (Exact Numeric Data Type)
  • will map to Decimal in .NET
  • 将映射到。net中的Decimal
  • both have (18, 0) as default (precision,scale) parameters in SQL server
  • 它们在SQL server中都有(18,0)作为默认参数(精度、比例)
  • scale = maximum number of decimal digits that can be stored to the right of the decimal point.
  • scale =可存储在小数点右边的最大小数位数。
  • kindly note that money(8 byte) and smallmoney(4 byte) are also exact and map to Decimal In .NET and have 4 decimal points(MSDN)
  • 请注意，货币(8字节)和smallmoney(4字节)也精确，并在。net中映射到小数，有4个小数点(MSDN)
  • decimal and numeric (Transact-SQL) - MSDN
  • 十进制和数字(Transact-SQL) - MSDN
• 数值= decimal(5到17个字节)(精确的数值数据类型)将在. net中映射为decimal。请注意，在。net中，money(8字节)和smallmoney(4字节)也是精确的，并映射到Decimal
• real (4 byte) (Approximate Numeric Data Type)
  • will map to Single in .NET
  • 将映射到。net中的Single
  • The ISO synonym for real is float(24)
  • real的ISO同义词是float(24)
  • float and real (Transact-SQL) - MSDN
  • float和real (Transact-SQL) - MSDN。
• real(4字节)(近似数值数据类型)将映射到。net中的Single。net中，real的ISO同义词是float(24) float(24)和real (Transact-SQL) - MSDN
• float (8 byte) (Approximate Numeric Data Type)
  • will map to Double in .NET
  • 在。net中会映射到双倍吗
• 浮点(8字节)(近似数值数据类型)将在。net中映射为双精度
• All exact numeric types always produce the same result, regardless of which kind of processor architecture is being used or the magnitude of the numbers
• 所有精确的数字类型总是产生相同的结果，无论使用哪种处理器体系结构或数字的大小
• The parameter supplied to the float data type defines the number of bits that are used to store the mantissa of the floating point number.
• 提供给浮点数据类型的参数定义用于存储浮点数的尾数的位数。
• Approximate Numeric Data Type usually uses less storage and have better speed (up to 20x) and you should also consider when they got converted in .NET
  • What is the difference between Decimal, Float and Double in C#
  • c#中小数、浮点数和双精度数的区别是什么
  • Decimal vs Double Speed
  • 十进制vs双速
  • SQL Server - .NET Data Type Mappings (From MSDN)
  • .NET数据类型映射(来自MSDN)
• 近似的数字数据类型通常使用较少的存储空间和更好的速度(最高可达20x)，您还应该考虑当它们在。net中被转换时，c# Decimal, Float和Double在c# Decimal中的区别是什么?

main source : MCTS Self-Paced Training Kit (Exam 70-433): Microsoft® SQL Server® 2008 Database Development - Chapter 3 - Tables , Data Types , and Declarative Data Integrity Lesson 1 - Choosing Data Types (Guidelines) - Page 93

主要来源:mct自学培训包(考试70 - 433):Microsoft®SQL Server 2008®数据库开发-第三章表、数据类型和声明式数据完整性课1 -选择数据类型(指南)- 93页

#2

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. Use the decimal data type to store numbers with decimals when the data values must be stored exactly as specified.

数字和小数数据类型的默认最大精度为38。在Transact-SQL中，数值在功能上等同于十进制数据类型。当数据值必须按照指定的方式存储时，使用decimal数据类型来存储带有小数的数字。

The behavior of float and real follows the IEEE 754 specification on approximate numeric data types. 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. 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. It is best to limit float and real columns to > or < comparisons.

float和real的行为遵循IEEE 754规范的近似数字数据类型。由于浮点数和实际数据类型的近似性质，当需要精确的数值行为时(例如在金融应用程序中)，在涉及四舍五入的操作中，或在等式检查中)，不要使用这些数据类型。相反，使用整数、小数、货币或smallmoney数据类型。避免在WHERE子句搜索条件中使用浮点或实列，特别是=和<>操作符。最好将浮动和实列限制为>或 <比较。< p>

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

我经常对十进制进行计算。在某些情况下，在进行任何计算之前，先将十进制值转换为浮点数，可以获得更好的精度。”

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

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

参考链接

#5

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. Regular Float is a single-precision floating point number. Double is a good combination of precision and simplicty for a lot of calculations. 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.

编辑(未能阅读整个问题):Float(53)(即real)是SQL Server中的一个双精度(32位)浮点数。规则浮点数是一个单精度浮点数。对于许多计算来说，双精度和简单性是很好的组合。您可以使用decimal(最高可达136位)创建一个非常高精度的数字，但您还必须注意，您必须正确地定义精度和缩放，以便将所有中间计算包含到所需的位数。

#6

Float is Approximate-number data type, which means that not all values in the data type range can be represented exactly.

Float是近似数字数据类型，这意味着数据类型范围中的所有值不能被精确地表示出来。

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.

Decimal/Numeric是固定精度的数据类型，这意味着数据类型范围内的所有值都可以精确地表示出来。你可以用十进制来省钱。

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. DECIMAL(2,2) and DECIMAL(2,4) are different data types. This means that 11.22 and 11.2222 are different types though this is not the case for float. For FLOAT(6) 11.22 and 11.2222 are same data types.

从小数或数字到浮点数的转换会导致精度的损失。对于十进制或数字数据类型，SQL Server将精度和比例的每个特定组合视为不同的数据类型。DECIMAL(2,2)和DECIMAL(2,4)是不同的数据类型。这意味着11.22和11.2222是不同的类型，尽管浮动不是这样的。对于FLOAT(6) 11.22和11.2222是相同的数据类型。

You can also use money data type for saving money. This is native data type with 4 digit precision for money. Most experts prefers this data type for saving money.

你也可以使用货币数据类型来省钱。这是具有4位精度的本机数据类型。为了省钱，大多数专家更喜欢这种数据类型。

Reference 1 2 3

参考1 2 3