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 (https://blog.sqlauthority.com), BOL DataTypes

,
Previous Post
SQL SERVER – Actual Execution Plan vs. Estimated Execution Plan
Next Post
SQL SERVER – 2005 – Find Database Collation Using T-SQL and SSMS

Related Posts

93 Comments. Leave new

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

    Reply
  • 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..

    Reply
  • 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 for the function

    Reply
  • 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.

    Reply
    • 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′

      Reply
  • Hi Madhivanan,

    i have this error

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

    please help.

    Thanks,

    Reply
  • 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,

    Reply
    • 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

      Reply
  • 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,

    Reply
  • Bryan Duchesne
    January 22, 2012 6:35 pm

    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?

    Reply
  • Emmanuel Damisa
    March 10, 2012 12:49 pm

    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.

    Reply
  • 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

    Reply
  • Need help in converting float “3538135420.163” to datetime. PLEASE HELP!

    Reply
  • 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

    Reply
    • Why do you want tto store numbers this way? It is difficult for doing arithmetic calculations. You can use this technique

      Reply
      • Actually its a variable number, according to our requirement we will store data accordingly. in that particular decimal value i have to find last number and have to add +1 to that?

  • 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 :)

    Reply
  • ny_giants_12
    July 17, 2012 9:37 am

    Hey how can I convert 0.0934 to 9.34 % and eventually round it off to 9 % .
    Can anyone guide me please ?

    Reply
  • Decimal ftvalue;
    double dblRate = Math.Round(Convert.ToDouble(ftvalue), 6);

    Eg: ftvalue = 123.123456789

    dblrate = 123.123456

    Reply
  • Converting from decimal or numeric to float or real can cause some loss of precision

    Why ????

    Reply
  • 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.

    Reply
  • microsoft says it must be follow the rule that:
    0 <= s <= p <= 38

    Reply
    • 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???????

      Reply
  • hello sir,
    What datatype is used?
    float, decimal or numeric for a financial transaction…
    thanks

    Reply
  • 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

    Reply

Leave a Reply

Menu