We help IT Professionals succeed at work.

MS SQL 2012 Add Column as Float Adds it as Real

ScuzzyJo
ScuzzyJo asked
on
Hi

This is really annoying me!  I have 2 lines which state:

ALTER TABLE ROSS_LIVE ADD Net_Amount float (20) default (0) NOT NULL;
GO

It creates the column but as a real, not a float.  Could someone please tell me what I'm doing wrong?  I've copied the code for another project/query I wrote and that one doesn't give me any problems.

I'm awarding 500 points for a quick and working solution.

Thanks
Sarah
Comment
Watch Question

Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Why do you say it's a real?

Author

Commented:
Hi Vitor

I right-clicked on the table, selected Design and that's what it told me it was.

Thanks
Sarah
IT Engineer
Distinguished Expert 2017
Commented:
Interesting. I did the test and got same result.
In MSDN article about float and real data types you can read: "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."
So I did a test by creating a column as float(25) and really shows as float. Or you've find a bug or it's really the behavior.

Author

Commented:
Wow!  You're right.  Thanks.  I changed it to 25 and it's now creating as a float.  That's really weird!  You don't always need a length of 25 when you create a float field.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
The default is 53! :O
Anyway if you need to store money values you have money and smallmoney data types.
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
The ISO synonym for real is float(24).
https://msdn.microsoft.com/en-AU/library/ms173773.aspx

i.e. It was already a "Float" but called a "Real" (confusing? yes, but I didn't make it that way)