The impetus for this post is a recent discussion we had in one of the forums with my fellow MVP friend.
To start off, run the below query in management studio editor and check the output
SELECT '2d5' + 2e6 + 1e-5e
If you've run this query by now you should be seeing an output like below
So what has happened here?
As you see from the above you get a floating point number as the result. The above statement is an excellent example of the various formats in which SQL Server identifies floating point numbers. The internal calculation happening would be as follows
2d5 - this is interpreted in alternative scientific notation as used by languages like Java,Python etc and corresponds to a value of 2 * 10^5
2e6 - this is interpretted as the standard E notation format for the floating number and would correspond to a value of 2 * 10 ^ 6
1e-5e - Here 1e-5 would be interpreted in E notation as in the above case to make the value as 1 * 10^-5 and last e would be interpreted as alias for the column
thus the total value would be calculated as
2*10^5 + 2*10^6 + 1* 10^-5=200000+2000000+.00001=2200000.00001
which is your obtained result
Thus from the above we can see that SQL Server interprets both E notation and alternate scientific notation values also in its floating point computations. This is underlined by the below check as well.
SELECT ISNUMERIC('2d5'),ISNUMERIC(2e6)
To start off, run the below query in management studio editor and check the output
SELECT '2d5' + 2e6 + 1e-5e
If you've run this query by now you should be seeing an output like below
So what has happened here?
As you see from the above you get a floating point number as the result. The above statement is an excellent example of the various formats in which SQL Server identifies floating point numbers. The internal calculation happening would be as follows
2d5 - this is interpreted in alternative scientific notation as used by languages like Java,Python etc and corresponds to a value of 2 * 10^5
2e6 - this is interpretted as the standard E notation format for the floating number and would correspond to a value of 2 * 10 ^ 6
1e-5e - Here 1e-5 would be interpreted in E notation as in the above case to make the value as 1 * 10^-5 and last e would be interpreted as alias for the column
thus the total value would be calculated as
2*10^5 + 2*10^6 + 1* 10^-5=200000+2000000+.00001=2200000.00001
which is your obtained result
Thus from the above we can see that SQL Server interprets both E notation and alternate scientific notation values also in its floating point computations. This is underlined by the below check as well.
SELECT ISNUMERIC('2d5'),ISNUMERIC(2e6)
No comments:
Post a Comment