# Applying algebraic and numeric functions in SQL Server 2008

## Expand even more on T-SQL mathematical functions with this breakdown of algebraic and numeric functions, including details on how they can be used to retrieve data.

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`

`SELECT 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:

Square |
SquareRoot |

16 |
2 |

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`

`SELECT 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:

Exponential |
Log |
Log10 |

54.5981500331442 |
1.38629436111989 |
0.602059991327962 |

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:

Power |

64 |

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`

`SELECT 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:

Round |
Floor |
Ceiling |

54.78 |
54 |
55 |

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`

`SELECT 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:

Absolute1 |
Absolute2 |
Absolute3 |

1.93 |
0 |
1.93 |

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`

`SELECT 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:

Sign1 |
Sign2 |
Sign3 |

1 |
0 |
-1 |

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:

`SELECT RAND()`

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:

`SELECT RAND(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:

`0.713759689954247`

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.*

## Please create a username to comment.