I am going to forge ahead and write another short article or tidbit on Transaction SQL math functions. Most, if not all, of these functions have been in the product before the release of SQL Server 2005.
I am very proud of my daughter making the advance math class in sixth grade. I am dedicating these blogs to her hard class work and love of the subject.
I will be exploring the natural logarithm LOG() and exponential EXP() and functions. The natural logarithm is based on the transcendental constant e or 2.71828182845905.
The natural logarithm of a number x is the power y to which e would have to be raised to equal x. It can also be defined as the area under the curve y = 1/x from 1 to a. It is a function that takes large numbers and makes them smaller.
The exponential function is the exact opposite or inverse function of the natural logarithm. Thus, exp(log(x)) is equal to x. It takes a small number and makes it bigger. Please see the detailed math articles on wikipedia for more information.
Both functions take any expression F as input which can be implicitly converted to float. The output of the function is a floating point number.
The examples below demonstrate various applications of the functions.
1 – How the exponential function takes a small number and makes it larger. Also, EXP(1) is the transcendental number.
2 – How the natural logarithm function takes a large number and makes it smaller. The LOG(e) is equal to one.
3 – Apply both EXP and LOG to a number x returns x.
4 – Last but not least, why we want to do a binary search. A 100 M row data set takes about 19 look ups to find the data.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
-- -- LOG versus EXP -- -- Exponent of the number SELECT EXP(10.0) AS EXPONENT, EXP(1) AS TRANSCENDENTAL, EXP(NULL) AS UNKNOWN; GO -- Natural logorithm of the number SELECT LOG(10.0) AS LOGORITHM, LOG(2.71828182845905) AS ONE, LOG(NULL) AS UNKNOWN; GO -- EXP & LOG inversely related SELECT EXP(LOG(654)) AS MYNUMBER; GO -- Huge number 100 M ~ 18.42 SELECT FORMAT(POWER(10, 8), 'N') AS LARGE_NO, LOG(POWER(10, 8)) AS SMALLER_NO |
The output from the test is listed below.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
output: EXPONENT TRANSCENDENTAL UNKNOWN ---------------------- ---------------------- ---------------------- 22026.4657948067 2.71828182845905 NULL LOGORITHM ONE UNKNOWN ---------------------- ---------------------- ---------------------- 2.30258509299405 1 NULL MYNUMBER ---------------------- 654 LARGE_NO SMALLER_NO ----------------- ---------------------- 100,000,000.00 18.4206807439524 |
To summarize, the TSQL language has support for both the natural logarithm and exponential functions. The exponential function is widely used in physics, chemistry, engineering, mathematical biology, economics and mathematics.
