SQL SERVER – Difference and Explanation among DECIMAL, FLOAT and NUMERIC

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

About these ads

87 thoughts on “SQL SERVER – Difference and Explanation among DECIMAL, FLOAT and NUMERIC

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

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

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

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

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

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

  7. Hello Everyone…

    what is the best data type to store number of hours ex.(0.00) is it Numeric/Decimal/Float?

    Please advise

  8. @JAM

    You can use datetime datatype for this, and if you are using sqlserver2008 you have time datattype also.Thanks

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

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

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

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

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

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

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

  16. Hello Taduri,

    The example you used here (45000 to 450.00) is not a data type conversion but a value change derived from devide by 100.
    You can convert a decimal like 45000 to 45000.00 by using below syntax:

    CONVERT(DECIMAL (10,2), yourvalue)

    Regards,
    Pinal Dave

  17. Hi,

    In my one project I am facing very strange problem of float.
    Using SQL query I am performing average of one float column using group by category column. If category have single brand then it will not produce same average.

    e.g. 7 will produce 7.0000000000000009

    Please help on this.

    Thanks in advance

    Regards,
    Vishal Jani

  18. Hi,

    I have the same problem here. If I do “select cast(’0.35′ as float)”, it gives me 0.34999999999999998 but only on 1 server out of 100. I realy don’t know what is the problem on that server.

    regards
    Tom

  19. Hi Pinal,
    I am having one float value in the database (Sql server 2008). In my form, I have two text boxes where first text box accepts the number of precision points and the second text box accepts the decimal number. If first text box is having value as ’2′ then second text box will accept the decimal number with 2 points precision(Ex.10.24). Now the problem is when I am saving this value in the database, it is adding some extra precisions to that floating point value like 10.2456098. I have to either save it exactly 10.24 in the database or while retreiving I should get it as 10.24. Can you tell me how to solve this problem? Please Help me.

    Thanks & Regards,
    Pavani.

    • There is no solving this. Floats (and other floating point numbers) are “inaccurate” by nature. This is why you should never store, for example, money values to float data types.

      You could try to use DECIMAL(18, 2) datatype instead of float. “18, 2″ means that you can have 0-18 digits (on either side of the decimal separator) with 0-2 decimals, so “10.24″ goes nicely to this data type.

      Btw. I just changed about 20 FLOAT types to DECIMAL(18,3) types in the DB/tables and went through ~80 stored procs and functions this morning and changed them to use correct data type. All the weight measurements were stored in floats and our billing is based on weight on some products so, well, customers do like to have accurate bills :)

      But I just can’t press this enough: If you don’t need floating point variables, and you most probably very rarely do, do not use them. Instead use fixed point variables like DECIMAL.

  20. Hello,

    I am doing this small exercise.

    declare @No decimal(38,5);
    set @No=12345678910111213.14151;

    select @No*1000/1000,@No/1000*1000,@No;

    Results are:

    12345678910111213.141510
    12345678910111213.141000
    12345678910111213.14151

    Why results of first 2 selects are different when mathematically it should be same.

    Why it is plz guide on this.

    • Did you ever receive a reply on this? Your problem is with your precision. there are five digits of precision. When you first divide by 1,000, you are moving the decimal point three positions to the left. Then, working back five spots from this new position, you only have two of your original decimal points left for precision. This would work correctly if you had decimal(38,8).

      12345678910111213.14151 / 1000 ==> 12345678910111.213141
      when you multiply that by your 1000 again, the decimal moves those three spots back to the right.

  21. hello budy

    i have a trouble for following example.

    input = 12.56333333 and want as out put 12.57

    does there any solution about this please send me.

    i have implemented this with multiple line of code but i thing this is wrong so please send me

    ” only single line snippet ”

    thanks in advance

    from

    kanthavadiya Amrut

  22. Hi

    I have one problem..please give me solution..it’s urgent

    DECLARE @data real
    SET @data =9.660606E+07
    declare @value varchar
    set @value = cast (cast(@data as decimal) as varchar)
    select @value
    SELECT
    CASE
    WHEN CHARINDEX(‘E’, @data) > 0 THEN CAST(LEFT(@data,
    CHARINDEX(‘E’, @data) – 1) AS FLOAT) * POWER(10.0, CAST(RIGHT(@data,
    CHARINDEX(‘E’, REVERSE(@data)) – 1) AS INT))
    ELSE CAST(@data AS BIGINT)
    END
    run this in sqlserver

    actual value 9.660606E+07 is 96606060
    but return 96606100
    i want exact value 96606060
    if you take decimal type instead of real..then run fine but

    The value from database comes is real type

  23. My last comment had the wrong email address.

    Using SQL Server 2005 i am trying to convert float values to datetime or time types.

    Can anyone help?

  24. I have values in my table 13123.84,10056.39,11441.98

    know my condition if decimal less than .50 then 10056 if greater than .50 then 10057. in sql server 2000. how i can get this results?

    please help me.

  25. First argument should be total no.of digits and the second one is precision.
    i.e decimal(3,2) allows 1.12 and decimal(2,1) allows 1.1

  26. Hi

    I have one problem while altering column datatype from Numeric to Integer. When I tried to alter it then its prompted with message “You should have to re-create the table to change etc…”. Also, earlier my database was running on sql server 2005 and now it is running on 2008.

    Please help me to solve this problem

    Thanks

  27. Hi, i have problem with rounding float data types. I am not getting rounded value correctly. For example when i run this,

    declare
    @vtest1 float(53),
    @vtest2 float(53)
    begin
    SET @vtest1 = 4551.935
    SET @vtest2 = 759.935

    SET @vtest1 = ROUND(@vtest1,2)
    SET @vtest2 = ROUND(@vtest2,2)

    print replace(rtrim(replace(ltrim(str(Cast(@vtest1 as float),25, 8)),’0′,’ ‘)),’ ‘,’0′)
    print replace(rtrim(replace(ltrim(str(Cast(@vtest2 as float),25, 8)),’0′,’ ‘)),’ ‘,’0′)
    end

    Output is

    4551.94
    759.93

    Can anyone explain me why there is difference in decimal (.93 vs .94). In both case i am expecting .94. How i can achieve this without changing float to numeric or decimal?

    Thanks.

  28. The numeric data type can store a maximum of 38 digits, all of which can be to the right of the decimal point. The numeric data type stores an exact representation of the number; there is no approximation of the stored value.

  29. Hi, i’m creating a trigger that checks the sum of a column if it is equal to 1.

    here’s my query:

    DECLARE @subtotal as numeric(19,5)
    SET @subtotal = 0.00000

    SELECT @subtotal = @subtotal+(SELECT CAST(ROUND(SUM(BaseQty),5) as NUMERIC(19,5)) FROM INSERTED WHERE Table1.ID = INSERTED.ID) FROM Table1 WHERE Table1.ID IN (SELECT DISTINCT ID FROM INSERTED)

    IF (@subtotal > 1.00000)
    BEGIN
    RAISERROR (‘Formulation is not equal to 1..’, 16, 10)
    END

    if the subtotal returns 1.4 and higher the result is true, but if subtotal is equal to 1.3, 1.2, 1.1 it returns false..

    can anybody tell me whats wrong with the query.?

    Thanks..

  30. Comparison of floating point values in SQL Server not always gives the expected result.

    With this function, comparison is done only on the first 15 significant digits. Since SQL Server only garantees a precision of 15 digits for float datatypes, this is expected to be secure.

    The function expects two inputs of default type float, that’s an implicit float(53)

    See http://floatequalsfloat.codeplex.com for the function

  31. I HAVE THE BELOW SCRIPT

    select DISTINCT expiry from Rec_Dtl where grn_number =27964 and m_code =’M605′

    THE RERSUTLS ARE

    exiry
    ——-
    382268; 07/02/05
    976131, 31/05/2011

    I WANT THE SEPRATE TWO VALUES LIKE BELOW.

    DISTINCT EXPIRY 1 , EXPRIY 2
    ————————————-
    382268 07/02/05
    976131 31/05/2011

    I WANT RESULTS EXACTLY LIKE ABOVE. PLEASE HELP ME.

    • Split it using substring

      select substring(expiry,1,charindex(‘;’,expiry)-1),substring(expiry,charindex(‘;’,expiry),len(expiry)) from Rec_Dtl where grn_number =27964 and m_code =’M605′

  32. Hi Madhivanan,

    i have this error

    Server: Msg 170, Level 15, State 1, Line 1
    Line 1: Incorrect syntax near ‘expiry’.

    please help.

    Thanks,

  33. HI madhivan,

    i adjust the script whic you’ve given. like below

    select distinct substring(expiry,1,charindex(‘;’,expiry)-1),substring(expiry,charindex(‘;’,expiry),len(expiry))
    from Rec_Dtl where grn_number =27964 and m_code =’M605′

    but know i have this error

    Server: Msg 536, Level 16, State 3, Line 1
    Invalid length parameter passed to the substring function.

    Thanks,

    • Do all values have ; as part of it? If not you may need to use different logic. Replace all comma into semicolon before running the code

      select substring(expiry,1,charindex(‘;’,expiry)-1),substring(expiry,charindex(‘;’,expiry),len(expiry))
      from (select distinct replace(expiry,’,’,’;’)) as expiry from Rec_Dtl where grn_number =27964 and m_code =’M605′) as t

  34. Hi madhivanan,

    the below scripts works well.

    select distinct substring(expiry,1,charindex(‘,’,expiry)-1),substring(expiry,charindex(‘,’,expiry),len(expiry))
    from Rec_Dtl where grn_number =27964 and m_code =’M605′

    and results.

    —————————— ——————————
    #3453453 ,$21-12-2014
    #S4345 ,$4YRS

    but i don’t want comma infront of 2nd column, like ,$21-12-2014 i need only $21-12-2014 without comma

    Thanks a lot for your support. May i have your email id please.

    Regards,

  35. I have a SQL procedure using OPENROWSET to import MS Foxpro data into a SQL table. When I run the procedure, I am getting errors on all my numeric field in the Foxpro table. What appears to be happening is if the DBF integer field is defined as 6,0 and contains 6 digits, it errors out. If less than 6 digits, it is fine. I thought I read somewhere that this is caused by a difference in the way that SQL treats numeric data over the way Foxpro does. Aside from physically changing the data definition in Foxpro (which I cannot do as the data I am importing is from a 3rd party program), is there any way to fix this problem?

  36. I have a question. I am new to .net platform. I am designing a database with sql 2008 and vb 2010. I want to create staff numbers that will be 5 digits and divisible by 7. I would have loved to make this primary key and auto increment. how do I do this? PLS HELP ME.

  37. If we want field of the table use sp_help in sql server query window.

    if you want any procedure or want to see what is in particular store procedure than short cut for this is sp_helptext

  38. I have a value as 1533.1.1 i have to add +1 for this?
    and aslo 1533.1.1.1 i have to add +1
    Result will be like 1533.1.2,1533.1.1.2.
    How to write a query in sqlserver

  39. I want to create a table with a variable accepting decimal numbers that has the decimal part like ( .53 or .23689) . How will i do this???
    Thanks in advance :)

  40. Hi,

    Why can’t a variable be used to create the scale when using cast as numeric?:

    declare @Scale as integer

    set @Scale = 2
    select cast(234.56789 as numeric(5, @Scale)

    This produces an error.

    Yet if using Round the variable works:

    declare @Scale as integer
    set @Scale = 2
    select ROUND(234.56789,@Scale)

    Any ideas? Our decimal precision varies according to a value elsewhere in the database.

    • Hi,
      I need an urgent help on conversion.
      I am doing BCP in a value 1,698,920.971 in a column of float datatype having length 8 and Precession 53 and Scale Null.

      But when i see the imported value in Database i see it like 1,698,920.97 but it should look like 1,698,920.971. Any Idea???????

  41. Getting the following error, please advice. ValidStartDates is a table containing datetime objects in 2 columns i.e StartDate and EndDate
    i.e.
    StartDate EndDate
    8/12/2009 12:00:00.000 AM | 8/19/2009 12:00:00.000 AM
    .
    .
    .
    .
    12/12/2012 12:00:00.000 AM | 12/19/2012 12:00:00.000 AM

    [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error converting expression to data type float.(22003,8115)

    drop table Factorials
    go

    with Dates(max_samples) as (SELECT count(*) from ValidStartDates),
    t(x, factorial) as (
    select 0, cast(1.0 as float)
    union all
    select x+1, factorial*(x+1)
    from t, Dates where x <= max_samples)
    select isnull(x, 0.0) as x, factorial
    into Factorials
    from t
    option (maxrecursion 300)
    go

  42. Pingback: SQL SERVER – Weekly Series – Memory Lane – #044 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s