2

I created a simple table as below:

create table table1
(
col1 float(1),
col2 float
)

When I check the table definition using sp_help or script table as create, I get the float(1) as real

CREATE TABLE [dbo].[table1](
[col1] [real] NULL,
[col2] [float] NULL
)

As per my understanding from msdn and stackoverflow references, float can have a precision n ranging from 1 to 53.

Why is it getting converted to real by default whenever float has a precision specified?

Community
  • 1
  • 1
Shriladha Balasubramanian
  • 43
  • 1
  • 1
  • 6

1 Answers1

3

SQL Server doesn't remember what language you used when you create the table - it just consults the current table definition and renders a piece of text that could have been used to create an identical table.

As the documentation points out, SQL Server doesn't support arbitrary precisions - it only supports two precisions - 24 and 53:

SQL Server treats n as one of two possible values. If 1<=n<=24, n is treated as 24. If 25<=n<=53, n is treated as 53.

As it also points out, real is treated as a synonym for float(24):

The ISO synonym for real is float(24).

So, any float column specified with a precision of 24 or lower will in fact be created as a float(24). And a synonym for float(24) is real. The system doesn't remember whether it was originally specified as float(1), float(24) or real.

Why is it getting converted to real by default whenever float has a precision specified?

It doesn't. As per the above, if you create a column with type float(25) (or any higher precision), you'll find it gets returned as plain float, rather than real, because it was created as a float(53) and 53 is the default precision when none is supplied.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448