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 (http://blog.SQLAuthority.com), BOL DataTypes
pinaldave
Hi,
just read your actual blog entry and found some typo:
You wrote “DECIMAL(2,4)” – I think you mean “DECIMAL(4,2)”…
Rumtata
It should be DECIMAL(4,2) and DECIMAL(6,4).
First arg is total no.of digits and the second is the precision…
-Jyothi
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: http://msdn.microsoft.com/en-us/library/ms173773.aspx
FLOAT(1-24) uses 4 bytes for storage.
FLOAT(25-53) uses 8 bytes for storage.
Therefore, the range is different.