Difference between numeric, float and decimal in SQL Server
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)
- 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)
- 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
- 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#
- Decimal vs Double Speed
- SQL Server - .NET Data Type Mappings (From MSDN)
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
What is the difference between NUMERIC and FLOAT in BigQuery?
I like the current answers. I want to add this as a proof of why NUMERIC
is necessary:
SELECT
4.35 * 100 a_float
, CAST(4.35 AS NUMERIC) * 100 a_numeric
This is not a bug - this is exactly how the IEEE defines floats should be handled. Meanwhile NUMERIC
exhibits behavior closer to what humans expect.
For another proof of NUMERIC
usefulness, this answer shows how NUMERIC
can handle numbers too big for JavaScript to normally handle.
Before you blame BigQuery for this problem, you can check that most other programming languages will do the same. Python, for example:
Why does a FLOAT give me a more accurate result than a DECIMAL?
I found the best precision to be when you use:
SELECT (CAST(297282.26 AS DECIMAL(15, 9)) / CAST(495470.44 AS DECIMAL(24, 2))) AS ResultDecimal
This gives a result of
0.599999991926864496699338915153
I think the actual value (to 100 digits) is:
0.5999999919268644966993389151530412187657451370862810705720405842980259326873264124495499670979362562...
Please bear in mind SQL Server defines the maximum precision and scale for division as:
max precision = (p1 - s1 + s2) + MAX(6, s1 + p2 + 1) -- up to 38
max scale = MAX(6, s1 + p2 + 1)
Where p1 & p2 are the precision of the two numbers and s1 & s2 are the scale of the numbers.
In this case the maximum precision is (15-9+2) + MAX(6, 9+24+1) = 8 + 34 = 42.
However SQL Server only allows a maximum precision of 38.
The maximum scale = MAX(6, 9+24+1) = 34
SQL Server: why is Float more accurate than Decimal
The decimal values that you have declared are fixed width, and there are no points after the decimal place. This affects the calculations.
SQL Server has a rather complex formula for how to calculate the precision of arithmetical expressions containing decimal numbers. The details are in the documentation. You also need to take into account that numeric constants are in decimal format, rather than numeric.
Also, in the end, you need to convert back to a decimal format with the precision that you want. In that case, you might discover that float
and decimal
are equivalent.
Difference between float and decimal data type
This is what I found when I had this doubt.
mysql> create table numbers (a decimal(10,2), b float);
mysql> insert into numbers values (100, 100);
mysql> select @a := (a/3), @b := (b/3), @a * 3, @b * 3 from numbers \G
*************************** 1. row ***************************
@a := (a/3): 33.333333333
@b := (b/3): 33.333333333333
@a + @a + @a: 99.999999999000000000000000000000
@b + @b + @b: 100
The decimal did exactly what's supposed to do on this cases, it
truncated the rest, thus losing the 1/3 part.
So for sums the decimal is better, but for divisions the float is
better, up to some point, of course. I mean, using DECIMAL will not give
you a "fail proof arithmetic" in any means.
Hope this helps.
Is there any difference between DECIMAL and NUMERIC in SQL Server?
They are the same. Numeric is functionally equivalent to decimal.
MSDN: decimal and numeric
datatypes in sql server , difference between similar dataypes, numeric, money , decimal ,float
Like everything else in SQL Server, it depends.
Numeric
is the same as Decimal
. These let you specify precision and are good if you will be having more than 2 decimal places or need more precision than what you indicated.
Money
is used for, you guessed it, money. If your field is a currency this is appropriate.
Float
is kind of special and is NOT suited for exact numbers. If you need to represent floating point numbers, this is a way to go. If you need to always store exactly what you put in the field, float
is a bad choice.
So, what do you want to use the field for? What does your data actually represent? That should be the determining factor in what datatype you use.
Related Topics
Activerecord Arel or Condition
How to Create a Parameterized SQL Query? Why Should I
Will Ansi Join Vs. Non-Ansi Join Queries Perform Differently
How to List All Foreign Keys Referencing a Given Table in SQL Server
Select N Random Rows from SQL Server Table
Convert Month Number to Month Name Function in Sql
Get Value Based on Max of a Different Column Grouped by Another Column
Difference Between Join and Union
Ruby Gem MySQL2 Install Failing
MySQL Select 10 Random Rows from 600K Rows Fast
Postgresql Unnest() With Element Number
Using Column Alias in Where Clause of MySQL Query Produces an Error
Aggregate Columns With Additional (Distinct) Filters
Oracle: How to Upsert (Update or Insert into a Table)