# Commonly Used Mathematical Functions In SQL Server

- Rafnas T P
- Jan 03, 2017
- 2.4k
- 0
- 6
- Blog

SQL Server has some default functions, where 8 mathematical functions are commonly used. Here, I am going to explain those mathematical functions, which are listed below.

- ABS
- Ceiling
- Floor
- Power
- Square
- SQRT
- Rand
- Round

First, let’s see where those mathematical functions are available in SQL Server. For this, open SQL Server Management Studio and go to Object Explorer Window -> expand Databases-> expand any Database (ex Sample), followed by expanding programmability folder->Functions->System Functions->mathematical functions from the sample database. Subsequently, you can see all the mathematical functions available in SQL Server. The screenshot is given below, which shows the mathematical functions.

**ABS (numeric expression) **It stands for absolute and this function returns the absolute (positive) number.

*Ex Select Abs (-506.6)*

Output 506.6 (without -sign)

Output 506.6 (without -sign)

**CEILING (numeric expression)**

It returns the smallest integer value, which is greater than or equal to the parameter.

*Ex1 Select CELING (50.2)*

Output 51

Ex2 Select CELING (-50.2)

Output -50

Output 51

Ex2 Select CELING (-50.2)

Output -50

**FLOOR (numeric expression)**

It returns the largest integer value, which is less than or equal to the parameter.

*Ex1 Select CELING (50.2)*

Output 50

Ex2 Select CELING (-50.2)

Output -51

Output 50

Ex2 Select CELING (-50.2)

Output -51

**POWER (numeric expression, power)**

It returns the power value of the specified expression to the specified power.

*Ex Select POWER (5, 2)*

Output 25

Output 25

**SQUARE (numeric expression)**

It returns the power square of the given expression.

*Ex Select SQUARE (5)*

Output 25

Output 25

**SQRT (numeric expression)**

It returns the square root of the given expression.

*Ex Select SQRT (25)*

Output 5

Output 5

**RAND ([seed_value])**

It returns a random float number between 0 and 1. RAND() of function takes an optional seed parameter. When seed value is supplied, RAND() function always returns the same value for the same seed.

**Ex1***Select RAND () --execute multiple times*

Output execution 1 0.526587984235786

execution 2 0.472586984532587

execution 3 0.895756244558586

Here, you will get output between 0 and 1 and the output will keep changing.

Output execution 1 0.526587984235786

execution 2 0.472586984532587

execution 3 0.895756244558586

*Here, you will get the output between 0 and 1 and the output will not be changing.*

Select RAND (1) --execute several time

Output execution 1 0.713581458935865

execution 2 0.713581458935865

execution 3 0.713581458935865

**Ex2**Select RAND (1) --execute several time

Output execution 1 0.713581458935865

execution 2 0.713581458935865

execution 3 0.713581458935865

**ROUND (numeric expression, length, [function parameter])**

It rounds the numeric expression, which is based on the given length. This function takes 3 parameters.

- Numeric_Expression is the number that we want to round.
- Length specifies the number of the digit, which we want to round to. If the length is a positive number, then the rounding is applied for the decimal part, whereas if the length is negative, the round is applied to the number before the decimal.

- The optional function parameter is used to indicate rounding or truncation operations. 0 indicates rounding, and non-zero indicates truncating, (default, if not specified is 0).

**Ex1**

--Round to 2 places after the decimal point

Select ROUND (650.536, 2)

Or

Select ROUND (650.536, 2, 0)

Output 650.540

**Ex2**

--Truncate anything after 2 places, after the decimal point

Select ROUND (650.536, 2, 1)

Output 650.530

**Ex3**

--Round to 1 place after the decimal point

Select ROUND (650.536, 1)

Or

Select ROUND (650.536, 1, 0)

Output 650.600

**Ex4**

--Truncate anything after 1 place, after the decimal point

Select ROUND (650.536, 1, 1)

Output 650.500

**Ex5**

--Round the last 2 places, before the decimal point

Select ROUND (650.536, -2)

Output 700.000