Why does SQL Server convert floats to scientific notation?

June 28, 2021 Andy DBA Stack Exchange 0

clip art of a double-quote character

Question

I came across some weird behavior: While passing a float value into a varchar column, the values are getting converted from integers into scientific notation, and it’s that scientific notation that gets stored as a string.

    if OBJECT_Id('tempdb..#whydis') is not null begin drop table #whydis end
    if OBJECT_Id('tempdb..#ImSeriously') is not null begin drop table #ImSeriously end

    create table #whydis (bigID float)
    create table #ImSeriously (bigID varchar(255))

    insert into #whydis(BigID)
    values(1495591),
    (1495289),
    (1495610),
    (1495611),
    (1495609),
    (1495592),
    (1495686)

    INSERT INTO #ImSeriously (bigID)
    SELECT BigID from #whydis

    select * from #ImSeriously

results look like this:

1.49559e+006

Scientific notation stored as a string. It’s easy enough to work around by casting as int:

    INSERT INTO #ImSeriously (bigID)
    SELECT cast(BigID as int) from #whydis

But the whole thing has me scratching my head.

Question: What is it about floats that stores them this way?

asked 2021-06-28 by James

Answer

The thing to remember about float as a datatype is that the values are just an estimate of a number. It’s not going to be an exact number.

As far as why SQL Server displays them in scientific notion? Because it is representing estimates, a float contains a truncated value that only has significance to a certain number of digits. This may be a throw-back to a lesson on "significant digits" in a science course you took in school. The notation used is expressly to help define the precision of the values being represented. Trailing zeros may or may not be part of that precision.

The docs are really helpful:

Values of float are truncated when they are converted to any integer type.

When you want to convert from float or real to character data, using the STR string function is usually more useful than CAST( ). This is because STR enables more control over formatting. For more information, see STR (Transact-SQL) and Functions (Transact-SQL).

If it is important to have the values retain accuracy and never be truncated, you should not use float.

You can see the different ways that different functions can be used to format floating-point numbers in this example, as well as seeing the truncation by using a larger (and smaller) number:

DECLARE @float float = 123456789012345678901234567890;
SELECT TheNumber = @float;

SELECT  ConvertWithoutStyle = CONVERT(varchar(255),@float),
        ConvertWithStyle0   = CONVERT(varchar(255),@float,0),
        ConvertWithStyle1   = CONVERT(varchar(255),@float,1),
        ConvertWithStyle2   = CONVERT(varchar(255),@float,2),
        ConvertWithStyle3   = CONVERT(varchar(255),@float,3);

SELECT  StrWith0Decimals    = STR(@float,30,0),
        StrWith1Decimals    = STR(@float,30,1),
        StrWith4Decimals    = STR(@float,30,4);

SELECT  FormatWorksToo      = FORMAT(@float,'####'),
        FormatWorksToo2     = FORMAT(@float,'####.##');


SET @float = 12.3456789012345678901234567890;
SELECT TheNumber = @float;

SELECT  ConvertWithoutStyle = CONVERT(varchar(255),@float),
        ConvertWithStyle0   = CONVERT(varchar(255),@float,0),
        ConvertWithStyle1   = CONVERT(varchar(255),@float,1),
        ConvertWithStyle2   = CONVERT(varchar(255),@float,2),
        ConvertWithStyle3   = CONVERT(varchar(255),@float,3);

SELECT  StrWith0Decimals    = STR(@float,30,0),
        StrWith1Decimals    = STR(@float,30,1),
        StrWith4Decimals    = STR(@float,30,4);

SELECT  FormatWorksToo      = FORMAT(@float,'####'),
        FormatWorksToo2     = FORMAT(@float,'####.##');

answered 2021-06-28 by Andy Mallon