Mathematical Functions

SQL Server 2005 offers several mathematical functions that can be used in your Transact-SQL code
Table  Mathematical Functions

Function	Description
ABS	Calculates the absolute value.
ACOS	Calculates the angle, the cosine of which is the specified argument, in radians.
ASIN	Calculates the angle, the sine of which is the specified argument, in radians.
ATAN	Calculates the angle, the tangent of which is the specified argument, in radians.
ATN2	Calculates the angle, the tangent of which is between two float expressions, in radians.
CEILING	Calculates the smallest integer greater than or equal to the provided argument.
COS	Calculates the cosine.
COT	Calculates the cotangent.
DEGRESS	Converts radians to degrees.
EXP	Calculates the exponential value of a provided argument.
FLOOR	Calculates the largest integer less than or equal to the provided argument.
LOG	Calculates the natural logarithm.
LOG10	Calculates the Base-10 logarithm.
PI	Returns the PI constant.
POWER	Returns the value of the first argument to the power of the second argument.
RADIANS	Converts degrees to radians.
RAND	Produces a random float type value ranging from 0 to 1.
ROUND	Rounds a provided argument's value to a specified precision.
SIGN	Returns –1 for negative values, 0 for zero values, and 1 if the provided argument is positive.
SIN	Calculates the sine for a given angle in radians.
SQUARE	Calculates the square of a provided expression.
SQRT	Calculates the square root.
TAN	Calculates the tangent.

Using Mathematical Functions
This recipe will demonstrate four different mathematical functions, including POWER, SQRT, ROUND,
and RAND.

This first example calculates 10 to the 2nd power:
SELECT POWER(10,2) Result
This returns:
---------
Result
---------
100


This next example calculates the square root of 100:
SELECT SQRT(100) Result
This returns:
-------
Result
-------
10

This example rounds a number to the third digit right of the decimal place:
SELECT ROUND(3.22245, 3) RoundedNumber
This returns:
------------------
RoundedNumber
------------------
3.22200

This example returns a random, float, data-type value between 0 and 1:
SELECT RAND() RandomNumber
This returns:
-----------------------
RandomNumber
-----------------------
0.497749897248417

This last example in the recipe returns a fixed, float, data-type value based on the provided
integer value:
SELECT RAND(22) Result
This returns:
-------------------
Result
------------------
0.713983285609346

How It Works
In this recipe, I demonstrated four different mathematical functions, including POWER, SQRT, ROUND, and RAND. Each function takes different parameters based on the operation it performs. For some mathematical functions, such as RAND, an input value is optional.