Part one of this series analyzed the fundamentals of T-SQL trigonometric functions in SQL Server2008 and provided examples of how these functions work. This next
section details additional mathematical functions -- algebraic and numeric-- with examples of each.
Working with algebraic functions
For the purposes of this article, I’ve included T-SQL functions specific to calculating the square root, exponential, or logarithm of a numerical value. The first two functions of this type are SQUARE and SQRT. The SQUARE function returns the square of a numerical value, and the SQRT function returns the square root, as shown in the following example:
DECLARE @root1 float
SET @root1 = 4
SQUARE(@root1) AS [Square],
SQRT(@root1) AS SquareRoot
First, I declared the @root1 variable as type float, and then assign a value of 4 to the variable, Next, I used a SELECT statement to run the SQUARE and SQRT functions against the variable. The SELECT statement returns the following results:
As you would expect, the square of 4 is 16 and the square root of 4 is 2.
Now let’s look at an example that includes the EXP, LOG, and LOG10 functions. The EXP function returns the exponential value of a float expression, the LOG function returns the value’s logarithm, and the LOG10 function returns its base-10 logarithm. The following example includes all three functions:
DECLARE @root2 float
SET @root2 = 4
EXP(@root2) AS Exponential,
LOG(@root2) AS [Log],
LOG10(@root2) AS [Log10]
After I declared and set the @root2 variable, I defined a SELECT statement that calls the three functions. For each function, I passed in the @root2 variable. The statement returns the following results:
As you can see, the statement returns the exponential, logarithm, and base-10 logarithm for the @root2 value, which is set at 4.
The final algebraic function that we’ll cover is POWER, which raises the value of a float expression to a specified power. The POWER function takes two arguments -- the base float expression and the power that the expression should be raised. In the following example, I raised the base value of 2 to the power of 6:
DECLARE @base float
SET @base = 2
DECLARE @power float
SET @power = 6
SELECT POWER(@base, @power) AS [Power]
To perform this function I declared and set two float variables. I used the @base variable as the first argument in the POWER function and the @power variable as the second argument. The statement returns the following results:
As you can see, a value of 2 raised to the power of 6 equals 64.
Working with numeric functions for T-SQL
I’ve categorized the final set of functions that we’ll review as numeric functions. Basically, these functions alter a numerical value in some way, such as rounding the number or providing its absolute positive value.
Three of these functions include ROUND, FLOOR, and CEILING. The ROUND function rounds a number to a specific precision, and the FLOOR function returns the largest integer less than or equal to the base value. The CEILING function returns the smallest integer greater than or equal to the base value. The following example demonstrates how these three functions work:
DECLARE @exp1 float
SET @exp1 = 54.784392
ROUND(@exp1, 2) AS [Round],
FLOOR(@exp1) AS [Floor],
CEILING(@exp1) AS [Ceiling]
First, I declared the @exp1 variable and set its value to 54.784392. I then included each of the functions in a SELECT statement. Notice that the ROUND function includes two arguments. The first argument is the base value. In this case, I specified the @exp1 variable. The second argument is 2, which indicates the precision that should be used when rounding the number. This is the number of decimal places that the results will include.
The FLOOR and CEILING functions each include one argument, which in this case is the @exp1 variable. The SELECT statement returns the following results:
The next function that we’ll cover is ABS, which returns the absolute, positive value of a numeric expression. The results returned by the function depend on whether the numerical expression is a positive or negative number, or whether it is 0. The following example demonstrates all three scenarios:
DECLARE @abs1 float
SET @abs1 = 1.93
DECLARE @abs2 float
SET @abs2 = 0
DECLARE @abs3 float
SET @abs3 = -1.93
ABS(@abs1) AS Absolute1,
ABS(@abs2) AS Absolute2,
ABS(@abs3) AS Absolute3
Notice that I declared and set three variables and then call the ABS function three times in a SELECT statement, one for each variable. The statement returns the following results:
As you can see, the variable values of 1.93 and -1.93 return the same results, and the variable value of 0 returns 0.
Another function concerned with the sign of a numerical expression is SIGN. The function returns a value of +1 if the input value is a positive number, a 0 if the input is 0, and a 1 if the value is negative. The following example demonstrates all three scenarios:
DECLARE @sign1 float
SET @sign1 = 1.93
DECLARE @sign2 float
SET @sign2 = 0
DECLARE @sign3 float
SET @sign3 = -1.93
SIGN(@sign1) AS Sign1,
SIGN(@sign2) AS Sign2,
SIGN(@sign3) AS Sign3
After I declared and set the three variables, I used the SIGN function in a SELECT statement to return the sign of each variable value, as shown in the following results:
As you can see, the variable value 1.93 returns a value of 1, the value 0 returns a 0, and -1.93 returns a -1.
The last function that we’ll cover is RAND. The function returns a random value from 0 to 1. The following statement demonstrates how this function can be used:
Each time you run this statement, it will return a different value within the 0 to 1 range. This is not the case, however, when you specify a seed value when calling the RAND function. For example, the RAND function in the following SELECT statement includes a seed value of 10:
Each time you call the RAND function with this seed value (in a single connection) it will return the same results. For instance, I ran the SELECT statement above repeatedly in a single connection and received the following results each time:
Notice that the value falls within the 0 to 1 range.
Summing up T-SQL mathematical functions
As you can see, you can use mathematical functions to take a variety of actions when working with numerical values. In the previous examples, I tried to demonstrate how the functions work and the types of data they return. Note that these examples don’t take into account all considerations when working with each function. For this reason, I recommend you refer to the Functions topic in SQL Server Books Online for specific details. In the meantime, you should now have a good foundation for working with T-SQL mathematical functions.
Back to part one: Trigonometric functions in SQL Server
ABOUT THE AUTHOR
Robert Sheldon is a technical consultant and the author of numerous books, articles, and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation. You can find more information at www.rhsheldon.com.
This was first published in June 2010