Thursday, December 31, 2015

SQL - EXP() Mathematical Function

SQL - EXP()
Exponential is a mathematical function which is almost always understood to mean the natural exponential function. The function EXP() returns the exponential value for the numeric expression. I have come across the EXP built in function in SQL Server and this function plays a very important role in data analytics calculations.
This function has one mandatory argument and it returns float type exponential value. In SQL, we have use the following syntax:
 SQL Syntax: EXP(Argument) Argument: Any numeric expression. Return Type: float. Note: If value is numeric and not equal to zero then it will return float type otherwise it will return 1 for the 0 value. if argument is null then return type should be null.
The exponential function is equal to ex. The value for the constant float number e is approximately 2.718281828 (natural logarithm).
----- declare local veriables
declare @inpfloat float=1.2,
@inpInt INT=1,
@inpDecimal Decimal=1.2

----- output parameters
select EXP(@inpfloat) as 'Float EXP()',
EXP(@inpINT) as 'Int EXP()',
EXP(@inpDecimal) as 'Decimal EXP()'

 Float EXP() Int EXP() Decimal EXP() 3.320116923 2.718281828 2.718281828
---- set value 0 for veriable
SET @inpfloat=0
SET @inpInt=0
SET @inpDecimal=0

----- output parameters
select EXP(@inpfloat) as 'Float EXP()',
EXP(@inpINT) as 'Int EXP()',
EXP(@inpDecimal) as 'Decimal EXP()'
 Float EXP() Int EXP() Decimal EXP() 1 1 1
We can view the difference on the above results. this is because 0 is to the power anything is equal to 1.
Conclusion
In simple words EXP() function is used to get the value of e ^ x because Exp expects a float and returns one, so the results from a decimal type include an implicit conversion to and from float and in turn this behaviour is due to inherent imprecision of FP numbers.