Why does SQL Server convert floats to scientific notation?

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?

This is documented under CAST and CONVERT:

For a float or real expression, style can have one of the values shown in the following table. Other values are processed as 0.

Value
Output

0 (default)
A maximum of 6 digits. Use in scientific notation, when appropriate.

1
Always 8 digits. Always use in scientific notation.

2
Always 16 digits. Always use in scientific notation.

3
Always 17 digits. Use for lossless conversion. With this style, every distinct float or real value is guaranteed to convert to a distinct character string.

Applies to: SQL Server (Starting in SQL Server 2016 (13.x)) and Azure SQL Database.

126, 128, 129
Included for legacy reasons; a future release could deprecate these values.

You are using an implicit conversion from float to varchar(255), which implicitly uses style 0. Your floats all have more than six digits, so they are represented in scientific notation.

Floating point numbers are often shown in scientific notation. These types are used when range is more important than absolute precision. The numbers quickly become unwieldy in other formats. Scientific notation also helps to emphasise the limited precision.

You might like to use STR or FORMAT instead:

DECLARE @f float;

SET @f = 123456789012345e294;

SELECT 
    LTRIM(STR(@f, 309, 0)),
    FORMAT(@f, 'F0');

Both produce the output:
12345678901234500000000000000000000000000000000000000000000000000000000
00000000000000000000000000000000000000000000000000000000000000000000000
00000000000000000000000000000000000000000000000000000000000000000000000
00000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000


For float, integers from −253 to 253 (−9,007,199,254,740,992 to 9,007,199,254,740,992) can be exactly represented. For real, integers between −16777216 and 16777216 can be exactly represented.

StackExchange Database Administrators Q#294935, answer score: 8