The basic difference between Decimal and Numeric :
They are the exactly same. Same thing different name.
The basic difference between Decimal/Numeric and Float :
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 reane can be represented exactly with precision and scale.
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.
Choosing Between DECIMAL and FLOAT in Real Tables
My simple rule: use an exact type for anything people will add up and check, like money, quantities and rates. Use FLOAT for scientific values, measurements and calculations where tiny rounding differences do not matter.
The exact types take a precision and a scale. Precision is the total number of digits, and scale is the number of digits after the point. The largest precision is 38. If you leave them out, you get a precision of 18 and a scale of 0, so CAST(1.5 AS NUMERIC) returns 2, not 1.5. Always write both numbers.
Storage grows with precision: 5 bytes for up to 9 digits, 9 bytes up to 19, 13 bytes up to 28 and 17 bytes up to 38. Pick the precision you really need rather than the maximum, since every extra byte is repeated in every row and every index that holds the column.
With FLOAT, avoid comparing values with =. Two calculations that should give the same answer can differ in the last digit and fail the test. Compare with a small tolerance, or round both sides first. FLOAT(24) and below is stored in 4 bytes, and FLOAT(25) up to FLOAT(53) in 8 bytes. When in doubt, choose the exact type, because a total that matches to the cent is much easier to explain.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
93 Comments. Leave new
What do you mean by data-type reane?
ITS DATA TYPE RANGE DEAR
Nice blog.very interesting topic, decimal float and numeric.
DECLARE @num float , @num1 Dec(19,2)
SET @num=950000.64
SET @num1=@num
SELECT CAST(@num1 as VARCHAR(18)),CAST(@num as VARCHAR(18)),@num
ideally all 3 should have same value. But I am getting different values..
SET @num1=@num is where an implicit conversion happened and you lost precision. Refer https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017
Even without using @num1 = @num, same reult is coming….
DECLARE @num float , @num1 Dec(19,2)
SET @num=950000.64
SET @num1=950000.64
SELECT CAST(@num1 as VARCHAR(18)),CAST(@num as VARCHAR(18)),@num
I also observed that LEN() function is behaving differently for float & decimal data types..
Example:
declare @a float, @b float, @c decimal(19,2)
select @a = 350470.88, @b = 35070.88, @c = 350470.88
select @a A, @b B, @c C, LEN(@a) len_A, LEN(@b) len_B, LEN(@c) len_C
Ideally len_A & len_C should have same value…
In T-SQL, I have a table with Lat/Long stored in real data types. However they only show up at scale of 4 such as -110.8130 / 31.3580. When I convert the column data types to Decimal (18,8), all of Lat/Long become -110.81304932 / 31.35803986. Is there any way I can control the “display” precision/scale when looking at real data type?
I thinks we can’t create a column with data type decimal(2,4) as precision will not be less than scale.