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.
Reference : Pinal Dave (https://blog.sqlauthority.com), BOL DataTypes
93 Comments. Leave new
Hi,
just read your actual blog entry and found some typo:
You wrote “DECIMAL(2,4)” – I think you mean “DECIMAL(4,2)”…
Rumtata
Hi Rumtata,
He wrote correct. why do you think that it should be DECIMAL(4,2)? He wrote DECIMAL(2,4) & that is correct. Because the corresponding value shown for that is 11.2222. Here precision is of 2 digits and scale is of 4.
hello mr. prashant,
Precision can not be less than scale. Hence, its a typo error.
when you declare a datatype DECIMAL(2,4), you get below error.
The scale must be less than or equal to the precision.
It should be DECIMAL(4,2) and DECIMAL(6,4).
First arg is total no.of digits and the second is the precision…
-Jyothi
No, its correct, suppose I need to have values like .25
Hi manish
if you have values like .25 then you can use DECIMAL(2,2)
yes Jyothi, you are correct
Good One
Hi . Some issues.
I have a table with a Real data type column and another one with float data type column.
table1 – real data type
table2 – float data type
insert into table1 values ( 22.22 )
insert into table2 select from table1
The value in table2 should have been 22.22 . But am getting some junk value in decimal part . Is this by design for SQL Server 2005 ?
Can anybody throw some light into this issue ? Thanks a lot for your time.
Regards,
Jay
we have a table with fields listed as float (8). i notice though some of values are stored as 3.4 and others are stored as 3.3999999999. can you explain why a number, if input as 3.4 would be stored as 3.3999999999? thanks.
To be even more precise [pun unintended :)],
the first argument is ‘precision’, i.e. the number of significant digits, which includes both the digits to the left and to the right of the decimal point.
the second argument is the ‘scale’, i.e. the number of digits to the right of the decimal point.
So Rumtata and Jyothi are both correct about the DECIMAL(4,2) and DECIMAL(6,4).
I just migrate my database from sql 2000 to sql 2005 and ran a test for writing data to dbf file (ms foxpro). The data in sql is ‘float’
for ex. 4000. but when this data is writen to dbf the format change to 4,000.00. this is a problem because now my dbf database see it as 4 instead of 4000. This is a major concern since we are dealing with inventory.
I’ve been looking in the net for a solution but haven’t came accross one yet.
Any suggestion on how I can fix this?
Thank you in advance for your help
Worth noting:- Numeric is appropriate in many places where money/currency is involved, but float is actually better for percentage columns….
Try this:-
Generate a table, called “Number” with 100 random numbers in it with 4 or more decimal places.
DECLARE @Total numeric(38,10)
SELECT @Total = SUM(Number) from Number
Select SUM(Percentage) FROM
(
SELECT Number / @Total as Percentage from
Number
) a
This doesn’t give 1.00 as it should…
But this does
Select SUM(Percentage) FROM
(
SELECT Convert(float,Number) / convert(float,@Total) as Percentage from
Number
) a
This is because although float doesn’t have perfect coverage across all values, it has much higher accuracy than numeric can manage.
Hello Everyone…
what is the best data type to store number of hours ex.(0.00) is it Numeric/Decimal/Float?
Please advise
@JAM
You can use datetime datatype for this, and if you are using sqlserver2008 you have time datattype also.Thanks
Hello Everyone,
I have one column with decimal(18,3) and column contains data like 1.050.
Now at the retrieval time i want data like 1.05 (omit extra 0’s from floating point)
Please help me in this.
Hi All
i am selecting a numric value (1260179004) through cursor and storing in a variable, when i am reading varaible then i am getting out put in Eponant ie 1.2 E009
, please can i get some idea to get numric values as it is stored in tables
@Syed
Try converting that values to decimal (38,2) and see if that works…
If you use numeric or real data types, you see those kind of outputs.
~ IM.
you are one man
hi Pinal,
Can you tell me what is the difference between float(24) and float(53) or in another words real and float.
as real is float(24) and by default float is float(54)
I am running this sql code in SQL 2005
–=================================
declare @a as float(24)
set @a=0.85
select cast ( @a as float(53))
–=================================
and the result is
0.850000023841858
Thanks.
@naresh
See the nhelp file here: https://docs.microsoft.com/en-us/sql/t-sql/data-types/float-and-real-transact-sql?view=sql-server-2017
FLOAT(1-24) uses 4 bytes for storage.
FLOAT(25-53) uses 8 bytes for storage.
Therefore, the range is different.
Hi,
I have a table with 2 million rows. There are four columns of data type decimal(19,6). The size of the table comes to around 250 MB ( in compressed mode) I created a new table with the columns as float data type and migrated the data. I compressed the table. The size of the table is now around 400 MB. Why is it this way
Hello Srini,
The size of table could increase because of new data type or because of Fillfactor. The Decimal value with scale upto 9 stores in 5 bytes while float could be taking 8 bytes.
Regards,
Pinal Dave
Hi, I’m still strugling with multiplying float with numbers as in example below. The first resultset is in type real, the second in type float. In this example my price = 22.23 and the factor 100000. In the real-example the result is correct, but in the float-example I would expect that the result would be 2223000, but it is 2222999.95422363, rounded to 2 decimals it’s 2222999.95. And that’s not correct. Any ideas how to solve this problem? Should I round to 2 or 4 decimals first and then multiply? Is there a general solution?
Thx.
declare @tReal table
(col1 real)
declare @tFloat table
(col1 float)
declare @Factor int
select @Factor = 100000
insert into @tReal values (22.23)
insert into @tFloat select col1 from @tReal
select col1 as Contents,
col1 * @Factor as MultiplyFactor
from @tReal
select col1 as Contents,
col1 * @Factor as MultiplyFactor
from @tFloat
I want to update decimal(don’t have scale) data with scale .
i.e., 45000 it shoul be convert like 450.00
any help ?