Real Maths

  • John Mitchell-245523 - Tuesday, September 19, 2017 8:31 AM

    Good question. Let's investigate.

    USE tempdb;

        -1 AS Col1
    ,    0 AS Col2
    ,    1 AS Col3
    ,    65 AS Col4
    ,    32768 AS Col5
    ,    2147483648 AS Col6
    ,    9223372036854775808 AS Col7
    ,    2.2 AS Col8
    ,    2.0000002 AS Col9
    ,    2.0000000000000002 AS Col10
    ,    2.0000000000000000000000000000000000002 AS Col11
    ,    222222.02 AS Col12
    ,    4.0/3.0 AS Col13
    INTO #MyTest;

    SELECT * FROM #MyTest;

    EXEC sp_help '#MyTest';

    Looks as if it uses int if it can, otherwise numeric with the smallest scale and precision possible.


    It annoys me that it picks numeric when bigint would do.  If it would choose bigint for integers that don't fit into int but do fit into bigint it would be easy to choose numeric instead by adding ".0" at the end of the integer.  Writing a cast or conversion call to get a bigint is a pain.  However we have to suffer that pain if we want columns with varchar(x) type (for specific values of  x) or nvarchar or smallint or tinyint which are often needed.
    It is of course easy to get it to create float columns, just use scientific notation for the value, for example a line like
    ,      1E0  as  Col14
    would lead to a column called Col14 with type float(53) containing the value 1.
    However,  getting float(24) (aka "real") requires writing a cast or conversion call.


  • Nice question to get the grey matter moving.  As is true so many times on this site, the discussion that follows is so very good.

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply