Commonly Used Mathematical Functions In SQL Server

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)

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

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

POWER (numeric expression, power)

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

Ex Select POWER (5, 2)
Output 25

SQUARE (numeric expression)

It returns the power square of the given expression.

Ex Select SQUARE (5)
Output 25

SQRT (numeric expression)
It returns the square root of the given expression.

Ex Select SQRT (25)
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.

Ex2

Select RAND (1) --execute several time
Output execution 1 0.713581458935865
execution 2 0.713581458935865
execution 3 0.713581458935865

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

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

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

1. Numeric_Expression is the number that we want to round.
2. 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.
   
3. 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