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.