SQL Server Mathematical Functions
Functions are objects that have a set of SQL statements. Each function accepts parameters as input and performs a sequence of operations before returning output. A single result set is returned by a function. SQL Server provides several mathematical functions to perform basic mathematical calculations. For example, we can use these functions to find the square root, logarithmic, round, floor, elementary exponential value, and trigonometric functions.
The following image will show all math functions used in SQL Server:
Math functions are divided into two categories: standard and common. These SQL Mathematical functions let you modify numerical data, which is essential for data processing. This article will cover most of the common math functions used in SQL Server and explain each with examples. The below table listed each of the maths functions with their definition:
| Function Name | Descriptions |
|---|---|
| ABS | This function is used to get the absolute value or absolute positive value. |
| ACOS | This function is used to get the angle in radians, whose cosine is the specified float expression. It is also known as arccosine. |
| ASIN | This function is used to get the angle in radians, whose sine is the specified float expression. It is also known as arcsine. |
| ATAN | This function is used to get the angle in radians, whose tangent is the specified float expression. It is also known as arctangent. |
| ATN2 | This function is used to get the angle (in radius) from the X-Axis to the specified point (y, x). Here, x and y are the two float expressions. |
| CEILING | This function is used to get the smallest value, which is greater than or equal to the specified numeric expression or given value. |
| COS | This function is used to get the trigonometric cosine value, which is measured in radians in the given expression. |
| COT | This function is used to get the trigonometric cotangent value, which is measured in radians in the given expression. |
| DEGREES | This function is used to convert a specified radiant angle into a degree-based equivalent angle. |
| EXP | This function is used to get an exponential value of the given float expression. |
| FLOOR | This function is used to get the largest integer value, which is less than or equal to the given numeric expression. |
| LOG | This function is used to get the natural logarithm of the given float expression with base E, where E is Euler’s number, which is equal to 2.71828. |
| LOG10 | This function is used to get the base 10 logarithmic value of the given float expression. |
| PI | This function is used to get the constant value of PI, which is approximately equal to 3.14 |
| POWER | This function is used to get the power of user-specified expression or value. |
| RADIANS | This function is used to convert the given angle to a Radians-measured equivalent angle. |
| RAND | This function is used to get the positive, and the value will be greater than or equal to 0.0 and less than 1. |
| ROUND | This function is used to get the nearest numeric value, which is rounded to the specified length or precision. |
| SIGN | This function is used to get the sign of the given expression that can be Positive (+), Negative (-), or Zero (0). |
| SIN | This method returns the trigonometric SINE value in radians for the given angle. |
| SQRT | This function is used to calculate the square root of a given number or value. |
| SQUARE | This function calculates the square of a specified value or an individual number. |
| TAN | This function is used to get the radian value of the trigonometric tangent of the given angle. |
Let us understand the most common mathematical functions with examples.
Example1: This example uses SQRT() function that accepts numeric values and displays the square root value:
Executing this statement will return the below output:
Example2: This example uses the ABS() function that accepts numeric values, either positive or negative, and display the absolute value (positive value):
Executing this statement will return the below output where we see both results has positive value only:
Example3: This example uses ACOS() function that accepts numeric values and displays the angle in radians, whose cosine is the specified float expression:
Executing this statement will return the below output:
NOTE: If we specify the value other than -1 to 1, this function returns NULL and report a domain error (invalid floating point operation).
Example4: This example uses ASIN() function that accepts numeric values and displays the angle in radians, whose sine is the specified float expression. It returns NULL and reports a domain error if the specified value is other than -1 to 1.
Executing this statement will return the below output:
It is another statement where we provide the value other than -1 to 1:
It displays the following error message:
Example5: This example uses CEILING() function that accept numeric values and display the next highest value in integer:
Executing this statement will return the below output:
Example6: This example uses the FLOOR() function that accepts numeric values and displays the next lowest value in integer:
Executing this statement will return the below output:
Example7: This example uses the RAND() function that accepts numeric values and displays the random float value between 0 and 1. It always returns the same output for the same seed.
Executing this statement will return the below output:
Example8: This example uses the DEGREES() function and displays the corresponding angle in degrees for an angle specified in radians.
Executing this statement will return the below output:
Example9: This example uses POWER() function and display the value of given expression to the specified power:
Executing this statement will return the below output:
Example10: This example uses the LOG() function and displays the natural logarithm of the given float expression. Its return type is float. If we specify the negative value, it returns an error message.
Executing this statement will return the below output:
Example11: This example uses the SIGN() function and display the positive (+1), zero (0), or negative (-1) sign of the given expression. Its return type is the same as the numeric expression.
Executing this statement will return the below output:
Example12: This example uses the RADIANS() function that converts the specified degree to an equivalent degree, which is measured in radians. Its return type is float.
Executing this statement will return the below output:
Conclusion
This article explains a complete overview of several SQL Server’s built-in mathematical functions, as well as some examples and outputs. ABS, DEGREES, CEILING, FLOOR, POWER, SIGN, and RADIANS are Arithmetic functions that return the same data type as the input value. Furthermore, the EXP, SQUARE, SQRT, LOG10, and LOG functions, among others, CAST their input values to the FLOAT data type and return the result as a FLOAT value.