434

What are the differences between numeric, float and decimal datatypes and which should be used in which situations?

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

ΩmegaMan
  • 29,542
  • 12
  • 100
  • 122
priyanka.sarkar
  • 25,766
  • 43
  • 127
  • 173
  • 2
    The decimal and numeric link above needs updating to https://learn.microsoft.com/en-us/sql/t-sql/data-types/decimal-and-numeric-transact-sql. The link above no longer exists. – Nigel Ainscoe Oct 02 '17 at 15:00
  • 3
    Usually, when dealing with financial subjects its interesting to work with **Integer** types, apart from **Floating-point** ones, and store the values as _cents_, instead of _dollars_, for example. – Pedro Feb 27 '18 at 17:58

8 Answers8

608

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

  • Approximate numeric data types(see table 3.3) 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. It is best to limit float and real columns to > or < comparisons. (Technet)

so generally choosing Decimal as your data type is the best bet if

  • your number can fit in it. Decimal precision is 10E38[~ 38 digits]
  • smaller storage space (and maybe calculation speed) of Float is not important for you
  • exact numeric behavior is required, such as in financial applications, in operations involving rounding, or in equality checks. (Technet)
  1. Exact Numeric Data Types decimal and numeric - MSDN
  • numeric = decimal (5 to 17 bytes)
    • 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.
    • money(8 byte) and smallmoney(4 byte) are also Exact Data Type and will map to Decimal in .NET and have 4 decimal points (MSDN)
  1. Approximate Numeric Data Types float and real - MSDN
  • real (4 byte)
    • will map to Single in .NET
    • The ISO synonym for real is float(24)
  • float (8 byte)
    • will map to Double in .NET

Exact Numeric Data Types Approximate Numeric Data Types

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

Ian Kemp
  • 28,293
  • 19
  • 112
  • 138
Iman
  • 17,932
  • 6
  • 80
  • 90
  • 22
    `use the float or real data types only if the precision provided by decimal is insufficient` - I thought real is LESS accurate then decimal ,so how come u write to use real if decimal is insufficient? – BornToCode Aug 20 '12 at 12:37
  • 10
    real is less accurate so is not recommended unless storing big numbers larger that decimal(> 10e38) is needed or space considerations .i guess the precision here in the quote means possible values and magnitude not the accuracy – Iman Aug 20 '12 at 13:26
  • 13
    @BornToCode The "precision" here refers to how broad the values you want to store are. if you need to store values between 1e10 and 1e-10, then `decimal` will be just fine. That's a precision of 20. If you need to store values between, say, 1e20 and 1e-20, well, `decimal` *can't* do that. That's 40 digits of precision. You can't ever store 1e20 and 1e-20 in the same `decimal` field. Instead, you can use `float`, which internally stores everything as a log of base 2. That allows a the full range of precision in one field with the drawback that only the first ~8 digits will be accurate. – Bacon Bits Nov 03 '15 at 15:49
  • 1
    I third BornToCode's and Iman's comments. I've just experimented (using SQL Server 2012), and it seems the machine epsilon for float(53), the highest-precision floating point type, is 2.22044604925031E-16. So you would get about 15 significant figures out of it. On the other hand, I can get 38 significant figures out of decimal. – Stewart May 02 '19 at 10:40
  • "Use the `float`..." - said who? Is that a quote or your opinion? – user443854 May 17 '19 at 13:34
  • @user443854 , as mentioned in the main source link it is written in official Microsoft MCTS book. saying "..only if.." seems to be a little harsh, but regarding all other factors it seems to be a safe bet. – Iman May 18 '19 at 07:04
26

Guidelines from MSDN: Using decimal, float, and real Data

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.

Cees Timmerman
  • 17,623
  • 11
  • 91
  • 124
kmote
  • 16,095
  • 11
  • 68
  • 91
  • The (fixed) number of decimals is specified in the `Scale` column. – Cees Timmerman Feb 10 '14 at 09:28
  • 1
    If you want 'exactly as specified' then, from a standards point of view, there is some advantage to `numeric` since it will never store with more precision than you asked for: see http://stackoverflow.com/a/759606/626804 – Ed Avis Apr 03 '17 at 10:33
15

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

Rahal Kanishka
  • 720
  • 13
  • 27
Stephan
  • 5,891
  • 1
  • 16
  • 24
  • The specifications have clearly been updated, and now the linked page does not mention `numeric` at all. – AntoineL Nov 22 '21 at 15:43
13

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

A-K
  • 16,804
  • 8
  • 54
  • 74
  • 3
    It doesn't make sense. All other answers, with sources, say that numeric or decimal data types are accurate, and float or real types are a very close approximation. Due to lower accuracy, I can understand that casting to float may allow faster calculations, but not higher precision. – cbaldan May 12 '16 at 18:05
  • 4
    All numeric data types can experience overflow and underflow. Overflow is an explicit error, however, underflow is silent. The characteristics of underflow for `decimal` and `float` are *different*. Decimal preserves against underflow as much as possible by increasing the precision or scale. However, once you hit the limit of significant digits in decimal, underflows are silent (and precision is lost). Float has a wider range of scale possible, and it's scale limitations that actually are the cause of underflow. Thus, float can have better scale. Nonetheless, it is still an *inexact* type. – ErikE Jun 10 '16 at 21:04
8

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')

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.

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, DECIMAL in most databases.

Varus Septimus
  • 1,510
  • 20
  • 13
7

Decimal has a fixed precision while float has variable precision.

EDIT (failed to read entire question): Float(53) (aka real) is a double-precision (64-bit) floating point number in SQL Server. Regular Float is a single-precision (32-bit) 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.

rmalayter
  • 519
  • 6
  • 12
Brian Reiter
  • 1,339
  • 1
  • 10
  • 16
  • You did not specify which is preferable while the case goes for financial transaction and why? – priyanka.sarkar Jun 29 '09 at 08:27
  • 1
    For SQL Server 2008 and higher, float(53) aka float is a double-precision (64-bit) floating point number, while float(24) aka real is a single-precision (32-bit) floating point number. [learn.microsoft.com/en-us/sql/t-sql/data-types/float-and-real-transact-sql](https://learn.microsoft.com/en-us/sql/t-sql/data-types/float-and-real-transact-sql) – M Kloster Dec 07 '18 at 15:32
7

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

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.

Reference 1 2 3

Somnath Muluk
  • 55,015
  • 38
  • 216
  • 226
3

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.

Be wary of the MONEY data type, it's of limited precision.

There is a lot of good information about it in the answers to this Stackoverflow question:

Should you choose the MONEY or DECIMAL(x,y) datatypes in SQL Server?

Simon Elms
  • 17,832
  • 21
  • 87
  • 103