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:

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.