# SQL Server Math/Numeric Functions

## SQL Server ABS() Function

Main Article :- Sql Server ABS() Function

The `ABS()` function returns the absolute (positive) value of the provided numeric expression.

Note: ABS converts negative to positive numbers. ABS has no impact on positive or negative numbers.

### Syntax:

The ABS function in SQL Server has the following syntax:

``ABS(numeric expression)``

Parameter Values

Parameter Description
numeric expression Required. The precise numeric or approximate numeric data type category is represented by an expression.

Technical Details

Works in: SQL Server, Azure SQL Database, Azure SQL Data Warehouse, and Parallel Data Warehouse are examples of Microsoft products (beginning with 2008).

### Examples:-

Example: This example shows how to find the absolute value of a number:

``````SELECT ABS(-235) AS AbsValueOfNegativeNumber,
ABS(-65.96) AS AbsValueOfNegativeNumber;``````

Result:

AbsValueOfNegativeNumber AbsValueOfDecimalNegativeNumber
235 65.96

The outcome demonstrates that the negative numeric value has been changed to a positive value.

## SQL Server ACOS() Function

The angle in radians for which the cosine is the provided float expression. Arc cosine is another name for it.

If the given value is not between -1 and 1, this function returns NULL.

### Syntax:

The ACOS() function in SQL Server has the following syntax:

``ACOS(float_number)``

Parameter Values

Parameter Description
float_number Required. A floating number.

Technical Details

Works in: SQL Server, Azure SQL Database, Azure SQL Data Warehouse, and Parallel Data Warehouse are examples of Microsoft products (beginning with 2008).

### Examples:-

Example 1: The arc cosine of an positive integer is returned in this example:

``SELECT ACOS(0.75) AS PostiveFloat;``

Result:

PostiveFloat
0.722734247813416

From the result, the specified number 0.75 convert into arc cosine value 0.722734247813416.

Example 2: The arc cosine of an negative integer is returned in this example:

``SELECT ACOS(-0.7) AS NegativeFloat;``

Result:

NegativeFloat
2.34619382340565

From the result, the specified number -0.7 convert into arc cosine value 2.34619382340565.

Example 3: Let's use zero and one in ACOS function:

``````SELECT ACOS(0) CosineZero,
ACOS(1) CosineOne;``````

Result:

CosineZero CosineOne
1.5707963267949 0

When you use zero and one in the ACOS function, it returns the arccosine value that corresponds to them.

## SQL Server ASIN() Function

The sine of the provided float expression provides the angle in radians. Arcsine is another name for this.

If the given value is not between -1 and 1, this function returns NULL.

### Syntax:

The ASIN() function in SQL Server has the following syntax:

``ASIN(float_number)``

Parameter Values

Parameter Description
float_number Required. A numeric value.

Technical Details

Works in: SQL Server, Azure SQL Database, Azure SQL Data Warehouse, and Parallel Data Warehouse are examples of Microsoft products (beginning with 2008).

### Examples:-

Example 1: The arc sine of a positive integer is returned in this example:

``SELECT ASIN(0.75) AS PostiveFloat;``

Result:

PostiveFloat
0.848062078981481

From the result, the specified number 0.75 convert into arc sine value 0.848062078981481.

Example 2: The arc sine of a negative integer is returned in this example:

``SELECT ASIN(-0.7) AS NegativeFloat;``

Result:

NegativeFloat
-0.775397496610753

From the result, the specified number -0.7 convert into arc sine value -0.775397496610753.

Example 3: Let's use zero and one in ASIN function:

``````SELECT ASIN(0) SinZero,
ASIN(1) SinOne;``````

Result:

SinZero SinOne
0 1.5707963267949

When you use zero and one in the ASIN function, it returns the arcsine value that corresponds to them.

## SQL Server ATAN() Function

A function that returns the angle in radians for which the tangent is a given float expression. Arctangent is another name for this.

### Syntax:

The ATAN() function in SQL Server has the following syntax:

``ATAN(float_number)``

Parameter Values

Parameter Description
float_number Required. An expression that is either of type float or implicitly converts to float.

Technical Details

Works in: SQL Server, Azure SQL Database, Azure SQL Data Warehouse, and Parallel Data Warehouse are examples of Microsoft products (beginning with 2008).

### Examples:-

Example 1: The arc tangent of a positive integer is returned in this example:

``SELECT ATAN(0.75) AS PostiveFloat;``

Result:

PostiveFloat
0.643501108793284

From the result, the specified number 0.75 convert into arc tangent value 0.643501108793284.

Example 2: The arc tangent of a negative integer is returned in this example:

``SELECT ATAN(-0.7) AS NegativeFloat;``

Result:

NegativeFloat
-0.610725964389209

From the result, the specified number -0.7 convert into arc tangent value -0.610725964389209.

Example 3: Let's use zero and one in ASIN function:

``````SELECT ATAN(0) TanZero,
ATAN(1) TanOne;``````

Result:

TanZero TanOne
0 0.785398163397448

When you use zero and one in the ATAN function, it returns the arctangent value that corresponds to them.

## SQL Server ATN2() Function

The `ATN2()` function returns the arc tangent of two integers.

### Syntax:

The ATN2() function in SQL Server has the following syntax:

``ATN2(float_number, float_number)``

Parameter Values

Parameter Description
float_number, float_number Required. The arc tangent is calculated using two numerical values.

Technical Details

Works in: SQL Server, Azure SQL Database, Azure SQL Data Warehouse, and Parallel Data Warehouse are examples of Microsoft products (beginning with 2008).

### Examples:-

Example 1: The ATN2 for the supplied x and y components is calculated in the following example:

``SELECT ATN2(-0.80, 2);``

Result:

-0.380506377112365

The arc tangent of two numbers is returned as a result.

Example 2: This example to find tangent value:

``SELECT ATN2(0.70, 1);``

Result:

0.610725964389209

The output gives value of tangent value.

## SQL Server CEILING() Function

Main Article :- Sql Server CEILING() Function

The `CEILING()` function delivers the least integer value greater than or equal to a given number.

### Syntax:

The CEILING() function in SQL Server has the following syntax:

``CEILING(numeric_value)``

Parameter Values

Parameter Description
numeric_value Required. A numeric value.

Technical Details

Works in: SQL Server, Azure SQL Database, Azure SQL Data Warehouse, and Parallel Data Warehouse are examples of Microsoft products (beginning with 2008).

### Examples:-

Example: This example shows positive numeric, negative numeric, and zero value inputs for the CEILING function:

``````SELECT CEILING(143.45) as Positive,
CEILING(-143.45) as Negative,
CEILING(0.0) as Zero;``````

Result:

positive negative zero
144 -143 0

From the result `CEILING()` function delivers the least integer value greater than or equal to a given number, if parameter is zero it will return Zero.

## SQL Server COS() Function

The trigonometric cosine of the supplied angle (calculated in radians) in the defined statement is returned by this `COS()` function.

### Syntax:

The COS() function in SQL Server has the following syntax:

``COS(float_number)``

Parameter Values

Parameter Description
Float_number Required. A numeric value.

Technical Details

Works in: SQL Server, Azure SQL Database, Azure SQL Data Warehouse, and Parallel Data Warehouse are examples of Microsoft products (beginning with 2008).

### Examples:-

Example 1: Returning the cosine of an integer is demonstrated in this example:

``````SELECT COS(15.75) AS PositiveCosValue,
COS(-0.1723921) AS NegativeCosValue;``````

Result:

PositiveCosValue NegativeCosValue
-0.999116586679734 0.985177246371465

The output defines the COS values of the specified angles.

Example 2: This is an instance of the PI() value returned by the COS() functions:

``SELECT COS(PI());``

Result:

-1

The PI() functions cosine value is -1.

Example 3: Let's use 1 and 0 inside cos() function:

``````SELECT COS(0) AS CosZero,
COS(1) AS CosOne;``````

Result:

CosZero CosOne
1 0.54030230586814

From the result cosine value of zero and 1 is returned.

## SQL Server COT() Function

The trigonometric cotangent of the supplied angle (calculated in radians) in the defined statement is returned by this `COT()` function.

### Syntax:

The COT() function in SQL Server has the following syntax:

``COT(float_number)``

Parameter Values

Parameter Description
number Required. A numeric value.

Technical Details

Works in: SQL Server, Azure SQL Database, Azure SQL Data Warehouse, and Parallel Data Warehouse are examples of Microsoft products (beginning with 2008).

### Examples:-

Example 1: The COT value for a certain angle is returned in this example:

``````SELECT COT(3) AS CotPositiveNumber,
COT(-2) AS CotNegativeNumber,
COT(5.22) AS CotFraction;``````

Result:

CotPositiveNumber CotNegativeNumber CotFraction
-7.01525255143453 0.457657554360286 -0.556226445571034

The result used to find the cotangent value of positive, negative, and fractions.

## SQL Server DEGREES() Function

The SQL Server `DEGREES()` function converts a radian angle to an approximate comparable value in degrees.

Note: RADIANS() and PI() methods are also available.

### Syntax:

The DEGREES() function in SQL Server has the following syntax:

``DEGREES(numeric_expr)``

Parameter Values

Parameter Description
numeric_expr Required. A angle in degree.

Technical Details

Works in: SQL Server, Azure SQL Database, Azure SQL Data Warehouse, and Parallel Data Warehouse are examples of Microsoft products (beginning with 2008).

### Examples:-

Example 1: This example used to Convert a radian value into degrees:

``SELECT DEGREES(PI()*2);``

Result:

360.0

The radian value of the expression inside the function is transformed to a degree, as shown in the result.

Example 2: The example to define the value of specified radianvalue:

``SELECT DEGREES(3.0);``

Result:

171.887338539246968594

The radian value of 3.0 converted into degree value 171.887338539246968594.

Example 3: If we use zero:

``SELECT DEGREES(0);``

Result:

0

When we use zero inside the DEGREES() function it return zero.

## SQL Server EXP() Function

The `EXP()` function returns the exponential value of a float expression.

The constant is the base of natural logarithms (2.718281...).

Note: `LOG()` method is also available.

### Syntax:

The EXP() function in SQL Server has the following syntax:

``EXP(float_number)``

Parameter Values

Parameter Description
float_number Required. Is a float or a type that can be implicitly converted to float expression.

Technical Details

Works in: SQL Server, Azure SQL Database, Azure SQL Data Warehouse, and Parallel Data Warehouse are examples of Microsoft products (beginning with 2008).

### Examples:-

Example: Return expression increased to the power of 2 is an example:

``SELECT EXP(3) as Expression;``

Result:

20.0855369231877

The exponential value of the supplied value 3 is returned in the following result.

## SQL Server FLOOR() Function

Main Article :- Sql Server FLOOR() Function

The `FLOOR()` function in SQL Server generates the greatest integer value similar to or less than a given number.

Check out the CEILING() and ROUND() methods as well.

### Syntax:

The FLOOR() function in SQL Server has the following syntax:

``FLOOR(float_number)``

Parameter Values

Parameter Description
float_number Required. Excluding the bit data type, is an expression of the exact or approximation numeric data type group.

Technical Details

Works in: SQL Server, Azure SQL Database, Azure SQL Data Warehouse, and Parallel Data Warehouse are examples of Microsoft products (beginning with 2008).

### Examples:-

Example: The FLOOR function is used in the preceding example to illustrate positive, negative, and currency values:

``````SELECT FLOOR(15.75) AS PositiveFloor,
FLOOR(-15.75) AS NegativeFloor,
FLOOR(\$15.75) AS CurrencyFloor;``````

Result:

PositiveFloor NegativeFloor CurrencyFloor
15 -16 15.0000

To return the largest integer number less than or equal to 15.75, -15.75, and \$15.75.

## SQL Server LOG() Function

In SQL Server, `LOG()` function returns the natural logarithm of the provided base of a given integer.

Also take a look at the `EXP()` method.

### Syntax:

The LOG() function in SQL Server has the following syntax:

``````LOG(float_number, base)

OR:

LOG(float_number)``````

Parameter Values

Parameter Description
float_number Required. The natural logarithm should be returned for this integer. It should be more than zero.
base Optional. The natural logarithm should be calculated on this basis. There must be several.

Technical Details

Works in: SQL Server, Azure SQL Database, Azure SQL Data Warehouse, and Parallel Data Warehouse are examples of Microsoft products (beginning with 2008).

### Examples:-

Example 1: The natural logarithms of 1 and 10 are returned in this example:

``````SELECT LOG(1) AS LogValueOfOne,
LOG(10) AS LogValueOfTen;``````

Result:

LogValueOfOne LogValueOfTen
0 2.30258509299405

The LOG for the supplied float expression is calculated as a result.

Example 2: The LOG for a number's exponent is calculated in the following example:

``SELECT LOG (EXP (15)) AS LogExpression;``

Result:

logexpression
15

The LOG of a number's exponent is calculated in the output.

## SQL Server LOG10() Function

The base-10 logarithm of the provided float expression is returned by the `LOG10()` function.

Take a look at the LOG() method as well.

### Syntax:

The LOG10() function in SQL Server has the following syntax:

``LOG10(float_number)``

Parameter Values

Parameter Description
float_number Required. The specified number greater than 0.

Technical Details

Works in: SQL Server, Azure SQL Database, Azure SQL Data Warehouse, and Parallel Data Warehouse are examples of Microsoft products (beginning with 2008).

### Examples:-

Example 1: The LOG10 of the supplied Fraction value is calculated in the following example:

``SELECT LOG10(7.5);``

Result:

0.8750612633917

The natural logarithm of log10 is calculated as a result.

Example 2: The LOG10 of the supplied value is calculated in the following example:

``SELECT LOG10(5);``

Result:

0.698970004336019

The natural logarithm of log10 is calculated as a result 0.698970004336019.

## SQL Server PI() Function

The constant float value of math `PI()` is returned by using this SQL Server function. Although SQL Server uses the entire double-precision value implicitly, the default number of decimal places presented is seven.

Note: that the `DEGREES()` and `RADIANS()` methods are also more or less same.

Features :

• This function is used to calculate pi's value.
• There are no parameters for this function.
• This function can do any type of mathematical operation, including addition, multiplication, and division.

### Syntax:

The PI() function in SQL Server has the following syntax:

``PI()``

Technical Details

Works in: SQL Server, Azure SQL Database, Azure SQL Data Warehouse, and Parallel Data Warehouse are examples of Microsoft products (beginning with 2008).

### Examples:-

Example 1: Example to return the value of PI:

``SELECT PI();``

Result:

3.1415926535897931

The result gives value of pi.

Example 2: Obtaining the default value of pi to the third decimal place:

``SELECT ROUND(PI(), 3) As PIValueDecimal;``

Result:

3.142

The value of pi is returned to three decimal places as a result of this calculation.

## SQL Server POWER() Function

Main Article :- Sql Server POWER() Function

The `POWER()` method returns the value of the input expression multiplied by the specified power.

### Syntax:

The POWER() function in SQL Server has the following syntax:

``POWER(numeric_value, power_value)``

Parameter Values

Parameter Description
numeric_value Required. The base number.
b Required. The exponent number.

Technical Details

Works in: SQL Server, Azure SQL Database, Azure SQL Data Warehouse, and Parallel Data Warehouse are examples of Microsoft products (beginning with 2008).

### Examples:-

Example 1: The example to return the second power of value:

``SELECT POWER(9, 2);``

Result:

81

The power of 8 is reutrn as 81.

Example 2: The example to check power of 3:

``SELECT POWER(4.0, 3) AS CubeValue;``

Result:

CubeValue
64.0

For the stated value of 4.0, the output is a power of 64.0.

## SQL Server RADIANS() Function

A degree value is converted to radians using the RADIANS() function.

When a numeric expression in degrees is given, the `RADIANS()` method outputs radians.

### Syntax:

The RADIANS() function in SQL Server has the following syntax:

``RADIANS(numeric_value)``

Parameter Values

Parameter Description
numeric_value Required. A number expressed in degrees.

Technical Details

Works in: SQL Server, Azure SQL Database, Azure SQL Data Warehouse, and Parallel Data Warehouse are examples of Microsoft products (beginning with 2008).

### Examples:-

Example 1: Converting a degree value to radians is demonstrated in this example:

``SELECT RADIANS(-60);``

Result:

-1

The output gives degree value 30 to radian value -1.

Example 2: Convert a degree value to radians with this example:

``SELECT RADIANS(210);``

Result:

3

The degree value 210 converted into radians 3.

## SQL Server RAND() Function

Main Article :- Sql Server RAND() Function

The `RAND()` function can be used to generate a random number or a range of random numbers between 0 (inclusive) and 1 (exclusive).

### Syntax:

1. The RAND() function in SQL Server has the following syntax:

``RAND(seed)``

2. Syntax for Random Decimal Range:

You can use the following formula to generate a random decimal number between two values (range):

``SELECT RAND()*(b-a)+a;``

3. Syntax for Random Integer Range:

The following formula can be used to generate a random integer number in the range of two values:

``SELECT FLOOR(RAND()*(b-a+1))+a;``

Parameter Values

Parameter Description
seed Optional. If seed is provided, it will produce a sequence of random integers. It generates a completely random number.

Technical Details

Works in: if no seed is supplied. SQL Server, Azure SQL Database, Azure SQL Data Warehouse, and Parallel Data Warehouse are examples of Microsoft products (beginning with 2008).

### Examples:-

Example 1: To generate a random value of the RAND() function without a seed value:

``SELECT RAND();``

Result:

0.775919974975617

When we do not mention the seed parameters, it will return a random integer between 0 and 1.

Example 2: The example to return a range of decimal number:

``SELECT RAND()*(15-10)+10;``

Result:

11.2898594585708

The result returns the decimal range between 10 and 15.

Example 3: The example to return a range of integer number:

``SELECT FLOOR(RAND()*(20-15+1)+15);``

Result:

19

The result returns the integer range between 15 and 25.

## SQL Server ROUND() Function

Main Article :- Sql Server ROUND() Function

The `ROUND()` function returns an integer with a specified number of decimal places rounded off.

Look into the `FLOOR()` and `CEILING()` methods as well.

### Syntax:

The ROUND() function in SQL Server has the following syntax:

``ROUND(numeric_value, decimal_value, operation)``

Parameter Values

Parameter Description
number Required. Rounding the number.
decimals Required. decimal number to round off.
operation Optional. If the result is zero, it is rounded to the next decimal place. If a value other than 0 is supplied, the result will be truncated to the defined number of decimals. The default value is zero.

Technical Details

Works in: SQL Server, Azure SQL Database, Azure SQL Data Warehouse, and Parallel Data Warehouse are examples of Microsoft products (beginning with 2008).

### Examples:-

Example 1: The examples below illustrate two expressions that demonstrate the use of ROUND:

``SELECT ROUND(249.426, 2, 1) AS RoundValue;``

Result:

249.420

Two expressions are included in the results that clarify how ROUND can be used. Always use an estimate for the last digit.

Example 2: Example of rounding a value to the -2 decimal place:

``SELECT ROUND(346.56, -2) AS RoundValue;``

Result:

300.00

The function will return a whole number when you use -2. It takes 10 as a base, so it will be based on the last two values before the point. And like that, when you use -2, it takes 100 as a base, so it will be based on the last three values before the point.

Example 3: Round the figure to zero decimal places in this example:

``SELECT ROUND(921.5796, 0);``

Result:

922.0000

The result is to round the numeric value based on 0, so that all decimal places will be removed.

Example 4: used to Round the Price column (to 1 decimal):

Let's took BOOKSIMPLE table to working on following query:

Name Price
The Practical SQL Handbook 136.33
Database Systems Using Oracle 99.99

The result will be look like this,

``````SELECT Price,
ROUND(Price, 1) AS RoundedPrice
FROM BOOKSIMPLE;``````

Result:

Price RoundedPrice
136.33 136.30
99.99 100.00

The result is used to round the nearest whole number based on 1 decimal in the table column.

## SQL Server SIGN() Function

The `SIGN()` function returns a value that indicates a number's sign.

One of the following values will be returned by this function:

• If number < 0, it returns 1
• If number = 0, it returns 0
• If number > 0, it returns -1

### Syntax:

The SIGN() function in SQL Server has the following syntax:

``SIGN(numeric_value)``

Parameter Values

Parameter Description
numeric_value Required. The number to return sign value.

Technical Details

Works in: SQL Server, Azure SQL Database, Azure SQL Data Warehouse, and Parallel Data Warehouse are examples of Microsoft products (beginning with 2008).

### Examples:-

Example 1: The SIGN values of three numbers are returned in the following example:

``````SELECT SIGN(-265) AS NegativeSign,
SIGN(0) AS Zero,
SIGN(543) AS PositiveSign;``````

Result:

NegativeSign Zero PositiveSign
-1 0 1

The result outputs a positive number, a negative number, and zero's SIGN value.

Example 2: Return the sign of a negative fractional number:

``SELECT SIGN(-0.5);``

Result:

-1.0

The sign of negative fraction -0.5 is -1.0.

Example 3: Return the sign of a positive fractional number:

``SELECT SIGN(0.5);``

Result:

1.0

The sign of positive fraction 0.5 is 1.0.

## SQL Server SIN() Function

The `SIN()` function implements the trigonometric sine of the supplied angle in radians as a numeric float expression.

### Syntax:

The SIN() function in SQL Server has the following syntax:

``SIN(numeric_value)``

Parameter Values

Parameter Description
numeric_value Required. A numeric value.

Technical Details

Works in: SQL Server, Azure SQL Database, Azure SQL Data Warehouse, and Parallel Data Warehouse are examples of Microsoft products (beginning with 2008).

### Examples:-

Example 1: Returning the sine of an integer as an example:

``````SELECT SIN(-1) AS NegativeInteger,
SIN(3) AS PositiveInteger;``````

Result:

NegativeInteger PositiveInteger
-0.841470984807897 0.141120008059867

The sine value of negative number and positive number.

Example 2: Returning the sine of an fractional value as an example:

``````SELECT SIN(60.652641) AS PositiveFraction,
SIN(-35.143567) AS NegativeFraction;``````

Result:

PositiveFraction NegativeFraction
-0.820554557001012 0.553072651326458

The sine value of negative fraction number and positive fraction number.

## SQL Server SQRT() Function

Main Article :- Sql Server SQRT() Function

The `SQRT()` function Returns the square root of the specified float value.

### Syntax:

The SQRT() function in SQL Server has the following syntax:

``SQRT(numeric_value)``

Parameter Values

Parameter Description
numeric_value Required. To compute the square root, you'll need a positive number.

Technical Details

Works in: SQL Server, Azure SQL Database, Azure SQL Data Warehouse, and Parallel Data Warehouse are examples of Microsoft products (beginning with 2008).

### Examples:-

Example 1: Returning the square root of an integer as an example:

``SELECT SQRT(81);``

Result:

9

The specified number 81 square root value is 9.

Example 2: Return the square root of a number:

``SELECT SQRT(33);``

Result:

5.74456264653803

The specified number 33 square root value is 5.74456264653803.

## SQL Server SQUARE() Function

The `SQUARE()` function calculates the square of a float value.

### Syntax:

The SQUARE() function in SQL Server has the following syntax:

``SQUARE(number)``

Parameter Values

Parameter Description
number Required. To calculate the square of a positive number.

Technical Details

Works in: There are three options: SQL Server (as of 2008), Azure SQL Data Warehouse, and Parallel Data Warehouse.

### Examples:-

Example 1: Returning the square of an integer as an example:

``SELECT SQUARE(11);``

Result:

121

The square value of 11 is 121.

Example 2: Example for return the square of a number:

``SELECT SQUARE(64);``

Result:

4096

The square value of 64 is 4096.

## SQL Server TAN() Function

The tangent of an integer is returned by the `TAN()` function.

### Syntax:

The TAN() function in SQL Server has the following syntax:

``TAN(numeric_value)``

Parameter Values

Parameter Description
number Required. A numeric value

Technical Details

Works in: SQL Server, Azure SQL Database, Azure SQL Data Warehouse, and Parallel Data Warehouse are examples of Microsoft products (beginning with 2008).

### Examples:-

Example 1: The following example returns the tangent of .45:

``SELECT TAN(.45);``

Result:

0.48

The result returns tangent value of 0.48.

Example 2: The example to return the tangent of a fractional number:

``SELECT TAN(2.50);``

Result:

-0.74702229723866

The result returns tangent value of -0.74702229723866.

Example 3: The tangent of PI()/2 is returned in the example below:

``SELECT TAN(PI()/2);``

Result:

1.63312393531954E+16

The output for Pi function in tan value 1.63312393531954E+16.