Tip

Applying algebraic and numeric functions in SQL Server 2008

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

    Requires Free Membership to View

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.

This was first published in June 2010

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.