I don't know the rules, I suspect that if the number of places to either the left or right of the '.' exceed some limit, the various clients that display floating point from SQL servers will fall back to scientific notation. SSMS seems to be rather sensitive to places to the right of the decimal point, not so much to the left.

I like to use CONVERT for on the fly formatting, but check out some of the interesting results when using it. These not exactly right representations happen because you cannot represent some numbers exactly in internal binary (base 2) formats like floating point, that you could otherwise spec exactly in base 10 numbering systems.

CREATE TABLE #T (T1 FLOAT)

INSERT INTO #T (T1) SELECT 0.000001

INSERT INTO #T (T1) SELECT 0.00001

INSERT INTO #T (T1) SELECT 0.0001

INSERT INTO #T (T1) SELECT 0.001

INSERT INTO #T (T1) SELECT 0.01

INSERT INTO #T (T1) SELECT 0.1

INSERT INTO #T (T1) SELECT 1.

INSERT INTO #T (T1) SELECT 10

INSERT INTO #T (T1) SELECT 100

INSERT INTO #T (T1) SELECT 1000

INSERT INTO #T (T1) SELECT 10000

INSERT INTO #T (T1) SELECT 100000

INSERT INTO #T (T1) SELECT 1000000

INSERT INTO #T (T1) SELECT 10000000

INSERT INTO #T (T1) SELECT 100000000

INSERT INTO #T (T1) SELECT 1000000000

INSERT INTO #T (T1) SELECT 10000000000

INSERT INTO #T (T1) SELECT 100000000000

INSERT INTO #T (T1) SELECT 1000000000000

INSERT INTO #T (T1) SELECT 1.1

INSERT INTO #T (T1) SELECT 10.1

INSERT INTO #T (T1) SELECT 100.1

INSERT INTO #T (T1) SELECT 1000.1

INSERT INTO #T (T1) SELECT 10000.1

INSERT INTO #T (T1) SELECT 100000.1

INSERT INTO #T (T1) SELECT 1000000.1

INSERT INTO #T (T1) SELECT 10000000.1

INSERT INTO #T (T1) SELECT 100000000.1

INSERT INTO #T (T1) SELECT 1000000000.1

INSERT INTO #T (T1) SELECT 10000000000.1

INSERT INTO #T (T1) SELECT 100000000000.1

INSERT INTO #T (T1) SELECT 1000000000000.1

SELECT T1 FROM #T

SELECT CONVERT(NUMERIC(36,18),T1) FROM #T