SQL Server 中数字、浮点数和十进制数的区别
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1056323/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Difference between numeric, float and decimal in SQL Server
提问by priyanka.sarkar
I searched in Google and also visited decimal and numericand SQL Server Helperto glean the difference between numeric, float and decimal datatypes and also to find out which one should be used in which situation.
我在谷歌搜索，还访问了小数和数字以及SQL Server Helper来收集数字、浮点数和小数数据类型之间的区别，并找出在哪种情况下应该使用哪一个。
For any kind of financial transaction (e.g. for salary field), which one is prefered and why?
对于任何类型的金融交易（例如工资领域），首选哪一种？为什么？
回答by Iman
use the floator realdata types only ifthe precision provided by decimal(up to 38 digits) is insufficient
仅当十进制（最多 38 位）提供的精度不足时才使用浮点数或实数数据类型
Approximate numeric data types do not store the exact values specified for manynumbers; they store an extremely closeapproximation of the value.(Technet)
Avoid using float or real columns in WHERE clause search conditions, especially the = and <> operators (Technet)
so generally because the precision provided by decimalis [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.
所以一般是因为decimal提供的精度是[10E38~38位]，如果你的数字可以容纳它，而Float较小的存储空间（可能还有速度）并不重要，处理异常行为和近似数字类型的问题并不重要可以接受，一般使用十进制。
more useful information
更有用的信息
- numeric = decimal (5 to 17 bytes) (ExactNumeric Data Type)
- will map to Decimal in .NET
- both have (18, 0) as default (precision,scale) parameters in SQL server
- scale = maximum number of decimal digits that can be stored to the right of the decimal point.
- 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)
- decimal and numeric (Transact-SQL) - MSDN
- real (4 byte) (ApproximateNumeric Data Type)
- will map to Single in .NET
- The ISO synonym for real is float(24)
- float and real (Transact-SQL) - MSDN
- float (8 byte) (ApproximateNumeric Data Type)
- will map to Double in .NET
- All exactnumeric 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 mantissaof 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
- 数字 = 十进制（5 到 17 个字节）（精确数字数据类型）
- 将映射到 .NET 中的十进制
- 两者都有 (18, 0) 作为 SQL Server 中的默认（精度，比例）参数
- scale = 小数点右侧可以存储的最大十进制位数。
- 请注意，money（8 字节）和 smallmoney（4 字节）也是精确的，并且映射到 .NET 中的十进制并有 4 个小数点（MSDN）
- 十进制和数字 (Transact-SQL) - MSDN
- 实数（4 字节）（近似数字数据类型）
- 将映射到 .NET 中的 Single
- real 的 ISO 同义词是 float(24)
- 浮点数和实数 (Transact-SQL) - MSDN
- 浮点数（8 字节）（近似数字数据类型）
- 将映射到 .NET 中的 Double
- 无论使用哪种处理器架构或数字的大小，所有精确的数字类型总是产生相同的结果
- 提供给 float 数据类型的参数定义了用于存储浮点数尾数的位数。
- Approximate Numeric Data Type 通常使用较少的存储空间并具有更好的速度（高达 20 倍），您还应该考虑它们何时在 .NET 中转换
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
主要来源： MCTS 自定进度培训套件（考试 70-433）：Microsoft？SQL服务器？2008 数据库开发- 第 3 章 - 表、数据类型和声明性数据完整性第 1 课 - 选择数据类型（指南） - 第 93 页
回答by kmote
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.
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.
数字和小数数据类型的默认最大精度为 38。在 Transact-SQL 中，数字在功能上等同于小数数据类型。当数据值必须完全按照指定的方式存储时，使用小数数据类型来存储带小数的数字。
float 和 real 的行为遵循关于近似数值数据类型的 IEEE 754 规范。由于浮点数和实数数据类型的近似性质，当需要精确的数值行为时不要使用这些数据类型，例如在金融应用程序中、在涉及舍入的操作中或在相等性检查中。而是使用整数、小数、货币或小货币数据类型。避免在 WHERE 子句搜索条件中使用浮点数或实数列，尤其是 = 和 <> 运算符。最好将 float 和 real 列限制为 > 或 < 比较。
回答by A-K
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. "
“我经常针对十进制值进行计算。在某些情况下，在任何计算之前将十进制值转换为浮点数，会产生更好的准确性。”
回答by Stephan
They Differ in Data Type Precedence
它们的数据类型优先级不同
Decimaland Numericare the same functionallybut there is still data type precedence, which can be crucial in some cases.
Decimal和Numeric在功能上是相同的，但仍然存在数据类型优先级，这在某些情况下可能是至关重要的。
SELECT SQL_VARIANT_PROPERTY(CAST(1 AS NUMERIC) + CAST(1 AS DECIMAL),'basetype')
The resulting data type is numericbecause it takes data type precedence.
结果数据类型是数字，因为它采用数据类型优先级。
Exhaustive list of data types by precedence:
按优先级列出的数据类型的详尽列表：
回答by Brian Reiter
Decimal has a fixed precision while float has variable precision.
Decimal 具有固定精度，而 float 具有可变精度。
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)（又名实数）是 SQL Server 中的双精度（32 位）浮点数。常规浮点数是一个单精度浮点数。对于许多计算，双精度是精度和简单性的良好组合。您可以使用十进制创建一个非常高精度的数字——最高 136 位——但您还必须小心地正确定义精度和标度，以便它可以包含所有中间计算到所需位数。
回答by Somnath Muluk
Floatis Approximate-number data type, which means that not all values in the data type range can be represented exactly.
Float是 Approximate-number 数据类型，这意味着并非数据类型范围内的所有值都可以精确表示。
Decimal/Numericis 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是 Fixed-Precision 数据类型，这意味着数据类型范围内的所有值都可以精确地表示为精度和小数位数。您可以使用十进制来省钱。
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.
从 Decimal 或 Numeric 转换为浮点数可能会导致一些精度损失。对于 Decimal 或 Numeric 数据类型，SQL Server 将精度和小数位数的每个特定组合视为不同的数据类型。DECIMAL(2,2) 和 DECIMAL(2,4) 是不同的数据类型。这意味着 11.22 和 11.2222 是不同的类型，尽管浮点数不是这种情况。对于 FLOAT(6) 11.22 和 11.2222 是相同的数据类型。
You can also use moneydata type for saving money. This is native data type with 4 digit precision for money. Most experts prefers this data type for saving money.
您还可以使用money数据类型来省钱。这是具有 4 位货币精度的本机数据类型。大多数专家更喜欢这种数据类型以节省资金。
回答by Varus Septimus
The case for Decimal
十进制的情况
What it the underlying need?
它的潜在需求是什么？
It arises from the fact that, ultimately, computers represent, internally, numbers in binary format. That leads, inevitably, to rounding errors.
它源于这样一个事实：最终，计算机在内部以二进制格式表示数字。这不可避免地导致四舍五入错误。
Consider this:
考虑一下：
0.1 (decimal, or "base 10") = .00011001100110011... (binary, or "base 2")
The above ellipsis [...] means 'infinite'. If you look at it carefully, there is an infinite repeating pattern (='0011')
上面的省略号 [...] 表示“无限”。仔细看，有一个无限重复的图案(='0011')
So, at some point the computer has to round that value. This leads to accumulation errors deriving from the repeated use of numbers that are inexactly stored.
因此，在某些时候，计算机必须舍入该值。这会导致由于重复使用不准确存储的数字而导致的累积错误。
Say that you want to store financial amounts (which are numbers that may have a fractional part). First of all, you cannot use integers obviously (integers don't have a fractional part).
From a purely mathematical point of view, the natural tendency would be to use a float. But, in a computer, floats have the part of a number that is located after a decimal point - the "mantissa" - limited. That leads to rounding errors.
假设您要存储财务金额（可能有小数部分的数字）。首先，您显然不能使用整数（整数没有小数部分）。从纯数学的角度来看，自然的趋势是使用float. 但是，在计算机中，浮点数具有位于小数点后的数字部分 - “尾数” - 有限。这会导致舍入错误。
To overcome this, computers offer specific datatypes that limit the binary rounding error in computers for decimal numbers. These are the data type that
should absolutely be used to represent financial amounts. These data types typically go by the name of Decimal. That's the case in C#, for example. Or, DECIMALin most databases.
为了克服这个问题，计算机提供了特定的数据类型来限制计算机中十进制数的二进制舍入误差。这些是绝对应该用于表示财务金额的数据类型。这些数据类型通常以Decimal. 例如，在 C# 中就是这种情况。或者，DECIMAL在大多数数据库中。
回答by Simon Tewsi
Although the question didn't include the MONEY data type some people coming across this thread might be tempted to use the MONEY data type for financial calculations.
尽管问题不包括 MONEY 数据类型，但遇到此线程的一些人可能会倾向于使用 MONEY 数据类型进行财务计算。
Be wary of the MONEY data type, it's of limited precision.
小心 MONEY 数据类型，它的精度有限。
There is a lot of good information about it in the answers to this Stackoverflow question:
这个 Stackoverflow 问题的答案中有很多关于它的好信息：
Should you choose the MONEY or DECIMAL(x,y) datatypes in SQL Server?
