asked
T-SQL EXP Function Return different results for SQL2012 to SQL2005
Hi I have come across the EXT built in function in SQL server and it seems this function returns different value in SQL2012 environment to SQL2005. running the code below returns different results in different environment. I'm trying to find out why there's the discrepancy and why the behavior of this function different from 2005 to 2012. This is quite Key to a project I'm working where A small difference in the calculations could have massive implications. `DECLARE @dec AS DECIMAL(20,18) DECLARE @flt AS FLOAT' 'SELECT @dec = (EXP(0.0267329893588985) - 1) SELECT @flt = (EXP(0.0267329893588985) - 1) SELECT @dec AS [Decimal], @flt AS [Float]` Results 2012 --------------------------------------------- Decimal Float 0.027093521247497640 0.0270935212474976 Results 2005 --------------------------------------------- Decimal Float 0.027093521247497860 0.0270935212474979
This site runs off of voting. Please indicate helpful answers by clicking on the thumbs up next to them. If any one answer lead to a solution, indicate that by clicking on the check mark next to that answer.
I don't know. I suspect you may be exposing some type of bug or the resolution of a bug. I would suggest getting in contact through [Microsoft Connect][1]. [1]:
http://connect.microsoft.com/
Make sure: - This is on the same hardware? - same charactersets, collation etc - same .net versions
From the SQL Server documentation for the [FLOAT data type][1]: > Floating point data is approximate; therefore, not all values in the data type range can be represented exactly. From the SQL Server documentation for the [EXP function][2]: > Returns the exponential value of the specified float expression. So you've got a conversion from your DECIMAL data type to an approximate data type, and are calling a function that operates on approximate data types and returns an approximate value... You're getting about 12 digits of accuracy here. I would suggest that, for calculations requiring a greater level of accuracy than this, then doing it in something other than the database engine might be the way to go... [1]:
http://technet.microsoft.com/en-us/library/ms173773.aspx [2]:
http://technet.microsoft.com/en-us/library/ms179857.aspx
I have tested this on different machine and same machine with the same bitness but still the same issue. Because the EXP function return float type I wonder if it's down to how the two versions treat or complies float. On BOL **2005** > The SQL Server float[(n)] data type complies with the SQL-92 standard for all values of n from 1 through 53. The synonym for double precision is float(53). **2012** > The SQL Server float[(n)] data type complies with the ISO standard for all values of n from 1 through 53. The synonym for double precision is float(53).
17 People are following this question.
