Page 1 of 3 123 LastLast
Results 1 to 15 of 33
  1. #1
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912

    Unanswered: Floating a real question

    Oh Hai!

    Does anyone know how floats and reals are stored in SQL Server purlease? For example, DateTime is a couple of four byte ints.

    Many thanks for your kind indulgence

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Ew. A floater....Oh, wait. This sounds suspiciously like homework...

    Easily confirmed, though;
    Code:
    create table test1
    (col1 float,
     col2 real)
    go
    sp_help test1
    Floats are 8 bytes, and reals are 4. No idea why they are different. For that, I might have to crack open the manual, and no one wants that. ;-)

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ah sorry - yes I know they are 4 & 8 bytes. 4 bytes is single precision, 8 bytes is double precision (as per the IEEE-754 standard)

    The reason I am asking is that I expected them to be BINARY(4) and BINARY(8) but that isn't what I'm observing.

    I've been playing around with some floating point numbers and they aren't behaving as I expect (in so far as they are more precise than they should be). I installed SQL 2000 to see if it was an engine update thing but they are the same in there.

    So before I get more confused I'm interested in what the underlying storage is.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    here's some examples.
    If I run MVJ's code from here:
    SQL Server Forums - Disasterous Decimal Debacle
    Code:
    select    n = 1, f=convert(float,1.4) union all
    select    2, convert(float,0.0000000000000001)  union all
    select    3, convert(float,1.4)+convert(float,0.0000000000000001)  union all
    select    4, convert(float,1.4)+convert(float,0.0000000000000002)
    My results are different:
    Code:
    n           f
    ----------- ----------------------
    1           1.4
    2           1E-16
    3           1.4
    4           1.4
    Similarly, 0.1 can't be represented in binary so this should not total to 1:
    Code:
    SELECT  shouldNotBe1    = SUM([float0.1]) 
    FROM    
            (
                SELECT  TOP 10 
                        [float0.1]   = 1e-1
                FROM    dbo.numbers
            ) AS f

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Typically, floats are stored in two parts (unlike Gaul). An Exponent, and a Mantissa. The exponent will take up only about 6 or 7 bits, and the Mantissa takes up the rest. Now, if you want to know which bits are the mantissa, and which are the exponent, you could store 5e-19 and 5e+20 to see which bits change. After that, you will start down the little-endian vs. big-endian rat hole.

    Overall, this is a bit academic. Slow day?

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I get 0.99999999999999989 on SQL 2008 R1 and SQL 2005 for your shouldNotBe1 query. Are you getting 1?

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by MCrowley View Post
    Slow day?
    Yes.

    Ok - so if we cast real to binary and pick it apart should it not then return as the same value out the other end?
    Code:
    DECLARE @r          AS REAL
          , @b          AS BINARY(4)--32 bit
    
    SELECT  @r          = 1e0/3
          , @b          = CAST(@r AS BINARY(4))
    
    SELECT realVal      = @r
         , realLen      = DATALENGTH(@r)
         , realBin      = @b
         --, realBinSign  = CASE WHEN SIGN(CAST(@b AS INT)) < 0 THEN 1 ELSE 0 END
    /*
    
    realVal        realLen    realBin
    0.3333333    4        0x3EAAAAAB
    
    Converting 3EAAAAAB to binary:
    111110101010101010101010101011
    
    Stick a couple of zeros to the beginning to make up 32 bits and vwala:
    
    1 8        23
    S EEEEEEEE FFFFFFFFFFFFFFFFFFFFFFF
    0 01111101 01010101010101010101011
    
    S is the sign of the mantissa, E the exponent and F is the decimal part of the mantissa.
    
    S=0, E=125 - 127 -> -2
    Denormalise:
    1.F * POWER(2e0, -2) =
    1.01010101010101010101011 (bin) * POWER(2e0, -2) = 
    0.0101010101010101010101011 (bin) = 
    0.333333343267441 (dec)

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by MCrowley View Post
    I get 0.99999999999999989 on SQL 2008 R1 and SQL 2005 for your shouldNotBe1 query. Are you getting 1?
    Yes I am.
    Code:
    SELECT @@VERSION
    --------------------------
    Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Maybe I have magic floats on my server.

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Code:
    select @@version
    
    SELECT  shouldNotBe1    = SUM([float0.1]) 
    FROM    
            (
                SELECT  TOP 10 
                        [float0.1]   = 1e-1
                FROM    sys.objects
            ) AS f
    gives me....
    Code:
                                                                                 
    -----------------------------------------------------------------------------
    Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86) 
    	Mar 29 2009 10:27:29 
    	Copyright (c) 1988-2008 Microsoft Corporation
    	Standard Edition on Windows NT 6.0 <X86> (Build 6001: Service Pack 1)
    
    
    (1 row(s) affected)
    
    shouldNotBe1                                          
    ----------------------------------------------------- 
    0.99999999999999989
    
    (1 row(s) affected)

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86) 
        Mar 29 2009 10:27:29 
        Copyright (c) 1988-2008 Microsoft Corporation
        Enterprise Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)
    
    
    (1 row(s) affected)
    
    shouldNotBe1
    ----------------------
    1
    
    (1 row(s) affected)
    Float computations can be affected by hardware, but not something like that I wouldn't have thought.

    The above is actually developer version, as is the version I ran it on at home. That can't be it can it Shirley? I'll try on SQL 2k again.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Here's another:
    Code:
    SELECT  3E-16 + 3E-16 + 3E-16 + 1E1, 1E1 + 3E-16 + 3E-16 + 3E-16
    I get 10 and 10. It should return two different answers.

  13. #13
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    It does for me. I am using Query Analyzer (I know. How retro). Management Studio runs with a number of other SET options. Amazingly, I get the same results you do.

    Now. How is SSMS screwing with you, today?

    Here is another handy thing to know. SSMS does not send comments to be "executed". If you run this:
    Code:
    select getdate()
    /*
    go
    */
    SSMS returns the current date and time. Query Analyzer, SQLCMD, OSQL, ISQL, and just about any other tool I have used returns
    Code:
    Server: Msg 113, Level 15, State 1, Line 3
    Missing end comment mark '*/'.
    Server: Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near '*'.

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Please can you run a trace (or just tell me them if you know them off hand) for the QA set options and I'll hard code them in to the script. I did have a look at all the SET options (especially the ANSI related ones) and saw none that looked relevant.

    here's my options from SSMS:
    Code:
    -- network protocol: LPC
    set quoted_identifier on
    set arithabort on
    set numeric_roundabort off
    set ansi_warnings on
    set ansi_padding on
    set ansi_nulls on
    set concat_null_yields_null on
    set cursor_close_on_commit off
    set implicit_transactions off
    set language us_english
    set dateformat mdy
    set datefirst 7
    set transaction isolation level read committed
    Dear god - I've just noticed my dev server language is set to American. That won't do, that won't do at all.

    I've also checked the server wide config options and none look relevant.

    I'm going to run these on a few other servers, see what I get.

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Another check I'd be grateful if you ran:
    it occurred to me that perhaps SSMS is doing some rounding for me (unlikely but I'm running out of ideas) so I thought I'd make sure the work is done server side.
    Code:
    SELECT  'YES'
    WHERE   CONVERT(FLOAT, 1.4) = CONVERT(DECIMAL(2, 1), 1.4)
    This returns YES for me - you?

    Also, running the code above on Standard & Enterprise 2005 & 2008 and also MSDE, running on XP, and 2k & 2k3 server all return the same for me. The only common thing is I always use SSMS 2008.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •