Selecting a decimal

Question

Post reply

Selecting a decimal

sql_only

Old Hand

Points: 361
More actions
January 6, 2016 at 5:29 am

#310658

why does
DECLARE @phi DECIMAL = (SQRT(5)+1)/2
SELECT @phi;
return 2
and not 1.618
You're all familiar with 'there's an app for that'... here's another... there's a CTE for that! 🙂

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

sql_only Old Hand Points: 361 More actions · Answer 1

was missing DECIMAL(18,16)...

You're all familiar with 'there's an app for that'... here's another... there's a CTE for that! 🙂

Phil Parkin SSC Guru Points: 247180 More actions · Answer 2

sql_only (1/6/2016)
was missing DECIMAL(18,16)...

Might as well use (19,16) or (19,17) – same space occupied on disk (ref. here).

sql_only Old Hand Points: 361 More actions · Answer 3

Phil Parkin (1/6/2016)
sql_only (1/6/2016)
was missing DECIMAL(18,16)...
Might as well use (19,16) or (19,17) – same space occupied on disk (ref. here).

Yes you're right.

I have had to settle with 2 though, I keep getting floating point errors if I make @phi more precise. I simply use @phi as a power to other decimals and the closest integer, 2, does not give these errors.

You're all familiar with 'there's an app for that'... here's another... there's a CTE for that! 🙂

Phil Parkin SSC Guru Points: 247180 More actions · Answer 4

sql_only (1/6/2016)
Phil Parkin (1/6/2016)
sql_only (1/6/2016)
was missing DECIMAL(18,16)...
Might as well use (19,16) or (19,17) – same space occupied on disk (ref. here).
Yes you're right.
I have had to settle with 2 though, I keep getting floating point errors if I make @phi more precise. I simply use @phi as a power to other decimals and the closest integer, 2, does not give these errors.

Out of interest, can you post some code which exemplifies one of these 'errors'?

sql_only Old Hand Points: 361 More actions · Answer 5

Phil Parkin (1/6/2016)
sql_only (1/6/2016)
Phil Parkin (1/6/2016)
sql_only (1/6/2016)
was missing DECIMAL(18,16)...
Might as well use (19,16) or (19,17) – same space occupied on disk (ref. here).
Yes you're right.
I have had to settle with 2 though, I keep getting floating point errors if I make @phi more precise. I simply use @phi as a power to other decimals and the closest integer, 2, does not give these errors.
Out of interest, can you post some code which exemplifies one of these 'errors'?

Well actually the code is a mess:-P

...and a lot.

Not sure its helpful

You're all familiar with 'there's an app for that'... here's another... there's a CTE for that! 🙂

Phil Parkin SSC Guru Points: 247180 More actions · Answer 6

sql_only (1/6/2016)
Phil Parkin (1/6/2016)
sql_only (1/6/2016)
Phil Parkin (1/6/2016)
sql_only (1/6/2016)
was missing DECIMAL(18,16)...
Might as well use (19,16) or (19,17) – same space occupied on disk (ref. here).
Yes you're right.
I have had to settle with 2 though, I keep getting floating point errors if I make @phi more precise. I simply use @phi as a power to other decimals and the closest integer, 2, does not give these errors.
Out of interest, can you post some code which exemplifies one of these 'errors'?
Well actually the code is a mess:-P
...and a lot.
Not sure its helpful

No worries. I did not want to see all of your code, just enough to generate one of these errors which you are talking about. But if you are happy with things as they are, no need.

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 7

sql_only (1/6/2016)
why does
DECLARE @phi DECIMAL = (SQRT(5)+1)/2
SELECT @phi;
return 2
and not 1.618

Integer math.

The data used as input in the formula are all integers, so the intermediate results are computed as integer as well. The conversion to decimal is only done when the final result has to be assigned to @phi.

See https://msdn.microsoft.com/en-us/library/ms190309.aspx for the gory details.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 8

Hugo Kornelis (1/6/2016)
sql_only (1/6/2016)
why does
DECLARE @phi DECIMAL = (SQRT(5)+1)/2
SELECT @phi;
return 2
and not 1.618
Integer math.
The data used as input in the formula are all integers, so the intermediate results are computed as integer as well. The conversion to decimal is only done when the final result has to be assigned to @phi.
See https://msdn.microsoft.com/en-us/library/ms190309.aspx for the gory details.

Actually, the SQRT function returns float which implicitly converts everything to float.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 9

Luis Cazares (1/6/2016)
Hugo Kornelis (1/6/2016)
sql_only (1/6/2016)
why does
DECLARE @phi DECIMAL = (SQRT(5)+1)/2
SELECT @phi;
return 2
and not 1.618
Integer math.
The data used as input in the formula are all integers, so the intermediate results are computed as integer as well. The conversion to decimal is only done when the final result has to be assigned to @phi.
See https://msdn.microsoft.com/en-us/library/ms190309.aspx for the gory details.
Actually, the SQRT function returns float which implicitly converts everything to float.

+1

I was typing that up and you beat me to it. 🙂

I'll just add that in this case the issue should have arisen because no scale was specified for the DECIMAL, and the default scale is 0.

Cheers!

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 10

Woops!

Thanks for catching my oversight, Luis!

And thank you for finding the actual explanation, Jacob!

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

drew.allen SSC Guru Points: 77014 More actions · Answer 11

Hugo Kornelis (1/6/2016)
sql_only (1/6/2016)
why does
DECLARE @phi DECIMAL = (SQRT(5)+1)/2
SELECT @phi;
return 2
and not 1.618
Integer math.

Actually, no. The SQRT function returns a decimal even though the input is an integer. The problem is that if you don't specify the precision and scale for DECIMAL it defaults to DECIMAL(18,0), so when you assign a decimal to the variable it rounds to the nearest integer.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA