*Past articles have broken down the basics of T-SQL string functions and aggregate*

#### Requires Free Membership to View

*functions. Now part one of this short series moves on to the specifics of T-SQL mathematical functions with details on how to manage trigonometric functions with SQL Server 2008, while part two examines algebraic and numeric functions.*

T-SQL mathematical functions in SQL Server 2008 perform numeric operations on numeric expressions. For example, you can use mathematical functions to retrieve an angle’s sine or cosine, return a value’s square or square root, or round a numeric value to a specified precision. Note, however, that mathematical functions should not be confused with arithmetic operators such as add (+) or subtract (-).

In this article, I’ll describe the mathematical functions available for SQL Server 2008 in three sections: trigonometric functions, algebraic functions, and numeric functions. This organization is meant to provide a rough structure for explaining the functions, and for comparing certain functions to one another. The functions are neither dependant on each other, nor do they need to be presented in a particular order.

**Working with trigonometric functions for T-SQL**

The first set of functions we’ll look at are* trigonometric
functions* -- those concerned with angles, radians, degrees, and the value of pi (π). Two of
the functions that fall into this category are **SIN** and **ASIN**. The SIN function returns
the sine of a specified angle. The ASIN function (also referred to as the arcsine function) returns
the angle of a specified sine.

**Note:** For an overview of the meaning of these and other
mathematical concepts referenced in this article, refer to a source that
focuses on trigonometry, algebra, or mathematics. For this article, I assume that you already
have a basic understanding of these terms.

Now let’s look at an example of how to use the SIN and ASIN functions. In the following set of statements, I’ve declared and set two variables and called them with a SELECT statement:

`DECLARE`` @angle1 float`

`SET`` @angle1 = 52.64`

`DECLARE`` @sine float`

`SET`` @sine = SIN(@angle1)`

`SELECT`

`@sine AS Sine,`

`ASIN``(@sine) AS Arcsine`

First, I created a variable called @angle1, which is configured with the float data type. Then, I set the value of the variable to 52.64.

Next, I created a variable called @sine, which is also a float value, and set the @sine variable value by using the SIN function to determine the sine of the @angle1 variable. The SIN function requires that the expression passed in as its argument is a float value or a numerical value that can be implicitly converted to a float.

**Note:** For any function that requires a float expression as
its argument, the value must be of float type or be able to be implicitly converted to
float.

Finally, I used a SELECT statement to retrieve the @sine value and the arcsine of that value. To determine the arcsine I used the ASIN function to calculate the sine’s angle. Since the function calculates a sine’s angle, the value passed to the function must range from -1 through 1. The function then returns the angle as radians. The following results show the sine for 52.64 and the arcsine of that sine value:

Sine |
Arcsine |

0.694032454911975 |
0.767075111026485 |

A set of functions similar to SIN and ASIN are the **COS** and **ACOS** functions. The COS
function returns the cosine of a specified angle and the ACOS function (referred to as the
arccosine function) returns the angle of a specified cosine. The following example demonstrates
these two functions:

`DECLARE`` @angle2 float
SET @angle2 = 52.64`

`DECLARE`` @cosine float
SET @cosine = COS(@angle2)`

`SELECT`

`@Cosine AS Cosine,`

`ACOS``(@cosine) AS Arccosine`

First, I declared the @angle2 variable as a float and set its value to 52.64. Next, I declared the @cosine variable, also as a float, and then used the COS function to set the value of @cosine to the cosine of @angle2. Finally, I created a SELECT statement that retrieves the value of @cosine and its arccosine. Notice that I used the ACOS function to retrieve the arccosine. The arccosine is displayed in radians, as shown in the following results:

Cosine |
Arccosine |

-0.719943714139416 |
2.37451754256331 |

As the results indicate, the cosine for 52.64 is -0.719943714139416, and the arccosine for the cosine value is 2.37451754256331.

Two other functions similar to the previous sets are **TAN** and **ATAN**. The TAN
function returns the tangent of a float expression, and the ATAN function returns a tangent’s
angle, in radians. The following example demonstrates both functions:

`DECLARE`` @angle3 float
SET @angle3 = 52.64`

`DECLARE`` @tangent float
SET @tangent = SIN(@angle3)`

`SELECT`

`@tangent AS Tangent,`

`ATAN``(@tangent) AS Arctangent`

As you can see, the example is nearly identical to the previous two, except that it returns the tangent and arctangent, as shown in the following results:

Tangent |
Arctangent |

0.694032454911975 |
0.606709662224033 |

SQL Server also supports the **COT** function, which returns the cotangent of a specified
angle, as shown in the following example:

`DECLARE`` @angle4 float`

`SET`` @angle4 = 52.64`

`DECLARE`` @cotangent float`

`SET`` @cotangent = COT(@angle4)`

`SELECT`` @cotangent AS Cotangent`

In this example, I declared the @angle4 variable and set its value to 52.64. Then I declared the @cotangent variable and set its value by using the COT function to retrieve the cotangent of @angle4. Next, I used a SELECT statement to call the @cotangent variable. The statement returns the following results:

Cotangent |

-1.03733436245532 |

Now let’s look at the **ATN2** function. The function returns the angle between the positive
x-axis and the line that runs from the origin to a specified point represented by x, y values. The
following example demonstrates how this works:

`DECLARE`` @x float`

`SET`` @x = 52.64`

`DECLARE`` @y float`

`SET`` @y = 192.79`

`SELECT`` ATN2(@x, @y) AS Angle`

Notice that I first declared and set the @x and @y variables and used them as arguments in the ATN2 function. Next, I used the ATN2 function in a SELECT statement to retrieve the angle, which is shown in the following results:

Angle |

0.266546088052137 |

As you can see, the angle between the x-axis and the ray defined by x, y is 0.266546088052137 radians.

In SQL Server, you can use the **RADIANS** function to convert degrees to radians and
use the DEGREES function to convert radians to degrees, as shown in the following example:

`DECLARE`` @degrees float`

`SET`` @degrees = 22.5`

`DECLARE`` @radians float`

`SET`` @radians = RADIANS(@degrees)`

`SELECT`

@radians AS [Radians],

DEGREES(@radians) AS
[Degrees]

To perform this function, I declared and set the @degrees variable to 22.5 degrees. Next, I declared and set the @radians variable using the RADIANS function to convert the @degrees value to radians. I then defined a SELECT statement to retrieve the @radians value. Then I used the DEGREES function, which converts the @radians value back to degrees, as shown in the following results:

Radians |
Degrees |

0.392699081698724 |
22.5 |

As you can see, 22.5 degrees converts to 0.392699081698724 radians, and when I converted those radians back to degrees, I got 22.5.

One more trigonometric function I want to point out is **PI**, which simply retrieves the
value of pi, as shown in the following example:

`DECLARE`` @pi float`

`SET`` @pi = PI()`

`SELECT`

`@pi AS [PI],`

`DEGREES``(@pi/2) AS [Degrees]`

First, I used the PI function to set the @pi variable to the value of pi, and then used that function in a SELECT statement where I retrieved the value of pi and converted @pi/2 to degrees. The following results show the data returned by the statement:

PI |
Degrees |

3.14159265358979 |
90 |

In addition to the value of pi, the results show that dividing pi by 2 gives you 90 degrees.

*Part two: Algebraic
and numeric 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.