Click here to Skip to main content
Click here to Skip to main content

SQL Server's Mathematical Functions

, 14 Jun 2014 CPOL
Rate this:
Please Sign up or sign in to vote.
SQL Server's Mathematical Functions expalined with examples

Table Of Contents

Introduction

The large majority of math functions are specific to applications using trigonometry, calculus and geometry. SQL Server Mathematical functions performs numeric operations on numeric expressions such as finding square root of a number or getting absolute value of number etc. The Mathematical functions should not be confused with Arithmetic Operators such as + (Add), - (Subtract), * (Multiply), / (Divide), % (Modulo).

This article will list down the available mathematical functions for SQL Server version from 2000 and above. If you wanted to check more details on any of the functions mentioned here, then click on the URLs mentioned in Reference section which will take you to MSDN site.

Background

The main objective of this article is to give a common place for SQL Server's mathematical functions, so that we don't need to jump from one page to another. This article will help you to understand the different mathematical functions available with different versions of SQL Server with simple examples (All examples are self descriptive), at the same time this article can be used as a quick reference too.

I have attached all the scripts used in this article as a .zip file.

Mathematical Functions – Quick Reference

A mathematical function performs a mathematical operation on numeric expressions and returns the result of the operation. The following scalar math functions perform a calculation, usually based on input values that are provided as arguments, and return a numeric value. All mathematical functions, except for RAND, are deterministic functions. This means they return the same results each time they are called with a specific set of input values. The RAND function is only deterministic when a seed parameter is specified.

Functions, such as ABS, CEILING, DEGREES, FLOOR, POWER, RADIANS, and SIGN, return a value having the same data type as the input value. Trigonometric and other functions, including EXP, LOG, LOG10, SQUARE, and SQRT, cast their input values to float and return a float value.

Note: Mathematical functions operate on the SQL Server system-supplied numeric data: decimal, integer, float, real, money, smallmoney, smallint, and tinyint. By default, the precision of built-in operations on float data type data is six decimal places.

Mathematical Functions - SQL Server 2000, 2005, 2008, 2008 R2, 2012, 2014
Function Description
ABS() A mathematical function that returns the absolute (positive) value of the specified numeric expression.
ACOS() A mathematical function that returns the angle, in radians, whose cosine is the specified float expression; also called arccosine.
ASIN() Returns the angle, in radians, whose sine is the specified float expression. This is also called arcsine.
ATAN() Returns the angle in radians whose tangent is a specified float expression. This is also called arctangent.
ATN2() Returns the angle, in radians, between the positive x-axis and the ray from the origin to the point (y, x), where x and y are the values of the two specified float expressions.
CEILING() Returns the smallest integer greater than, or equal to, the specified numeric expression.
COS() Is a mathematical function that returns the trigonometric cosine of the specified angle, in radians, in the specified expression.
COT() A mathematical function that returns the trigonometric cotangent of the specified angle, in radians, in the specified float expression
DEGREES() Returns the corresponding angle in degrees for an angle specified in radians.
EXP() Returns the exponential value of the specified float expression.
FLOOR() Returns the largest integer less than or equal to the specified numeric expression.
LOG() Returns the natural logarithm of the specified float expression in SQL Server.
LOG10() Returns the base-10 logarithm of the specified float expression.
PI() Returns the constant value of PI.
POWER() Returns the value of the specified expression to the specified power.
RADIANS() Returns radians when a numeric expression, in degrees, is entered.
RAND() Returns a pseudo-random float value from 0 through 1, exclusive.
ROUND() Returns a numeric value, rounded to the specified length or precision.
SIGN() Returns the positive (+1), zero (0), or negative (-1) sign of the specified expression.
SIN() Returns the trigonometric sine of the specified angle, in radians, and in an approximate numeric, float, expression.
SQRT() Returns the square root of the specified float value.
SQUARE() Returns the square of the specified float value.
TAN() Returns the tangent of the input expression.
   

For a better understanding, Let's see all the above functions with sample data / query.

Mathematical Functions – In Detail

Now we will go through each functions with sample codes.

ABS

The ABS function returns the absolute (positive) value of its parameter (numeric expression). It converts negative values to positive values but leaves positive values unchanged. The value being processed can be any numeric data type and the function returns a value of the same type.

Syntax:-

ABS ( numeric_expression ) 

Here 'numeric_expression' as an expression of the exact numeric or approximate numeric data type category and the function returns the same type as numeric_expression.

Example:-

The following example shows the results of using the ABS function on three different numbers.

SELECT ABS(123), ABS(-123), ABS(-123.45) --        123, 123, 123.45

The ABS function can produce an overflow error when the absolute value of a number is greater than the largest number that can be represented by the specified data type.

DECLARE @i int; SET @i = -2147483648;SELECT ABS(@i);GO 

INT data type can hold only values that range from -2,147,483,648 to 2,147,483,647. Computing the absolute value for the signed integer -2,147,483,648 causes an overflow error because its absolute value is greater than the positive range for the int data type

Msg 8115, Level 16, State 2, Line 3
Arithmetic overflow error converting expression to data type int.     

ACOS

A mathematical function that returns the angle, in radians, whose cosine is the specified float expression; also called arccosine.

Syntax:-

ACOS ( float_expression )  

Here 'float_expression' is an expression of the type float or of a type that can be implicitly converted to float, with a value from -1 through 1. This function returns a float value.

Note: Values outside this range return NULL and report a domain error

Example:-

The following example returns the ACOS of the specified number.

SET NOCOUNT OFF; 
DECLARE @cos float;
SET @cos = -1.0;
SELECT 'The ACOS of the number is: ' + CONVERT(varchar, ACOS(@cos)); 
--The ACOS of the number is: 3.14159  

ASIN

Returns the angle, in radians, whose sine is the specified float expression. This is also called arcsine.

Syntax:-

ASIN ( float_expression )

Here 'float_expression' is an expression of the type float or of a type that can be implicitly converted to float, with a value from -1 through 1. This function returns a float value.

Note: Values outside this range return NULL and report a domain error.

Example:-

The following example takes a float expression and returns the ASIN of the specified angle.

DECLARE @angle float
SET @angle = 0.1472738
SELECT 'The ASIN of the angle is: ' + CONVERT(varchar, ASIN(@angle))
GO 
-- The ASIN of the angle is: 0.147811 

ATAN

Returns the angle in radians whose tangent is a specified float expression. This is also called arctangent.

Syntax:-

ATAN ( float_expression )

Here 'float_expression' is an expression of the type float or of a type that can be implicitly converted to float. This function returns a float value.

Example:-

The following example takes a float expression and returns the ATAN of the specified angle.

DECLARE @angle float
SET @angle = 0.1472738
SELECT 'The ATAN of the angle is: ' + CONVERT(varchar, ATAN(@angle))
GO 
-- The ATAN of the angle is: 0.146223 

ATN2

Returns the angle, in radians, between the positive x-axis and the ray from the origin to the point (y, x), where x and y are the values of the two specified float expressions.

Syntax:-

ATN2 ( float_expression , float_expression )

Here 'float_expression' is an expression of the float data type. This function returns a float value.

Example:-

The following example calculates the ATN2 for the specified x and y components.

DECLARE @x float = 35.175643, @y float = 129.44;
SELECT 'The ATN2 of the angle is: ' + CONVERT(varchar,ATN2(@x,@y ));
GO
--The ATN2 of the angle is: 0.265345  

CEILING

Returns the smallest integer greater than, or equal to, the specified numeric expression. If the numeric expression is negative then it returns the next highest negative whole number. The return data type is same as the argument data type.

Syntax:-

CEILING ( numeric_expression )

Here 'numeric_expression' is an expression of the exact numeric or approximate numeric data type category, except for the bit data type. The return data type is same as the argument data type.

Note: if numeric expression contains a decimal number then it will convert to the next greatest whole number and sum up with the integer value else it returns the whole number given in the argument.

Example:-

The following example shows positive numeric, negative, and zero values with the CEILING function.

SELECT CEILING($123.45), CEILING($-123.45), CEILING($0.0);
GO
-- 124.00    -123.00    0.00  

COS

Returns the trigonometric cosine of the specified angle, in radians, in the specified expression.

Syntax:-

COS ( float_expression )

Here 'float_expression' is an expression of type float and this function returns a float value.

Example:-

The following example returns the COS of the specific angle.

DECLARE @angle float;
SET @angle = 14.78;
SELECT 'The COS of the angle is: ' + CONVERT(varchar,COS(@angle));
GO
--The COS of the angle is: -0.599465 

COT

A mathematical function that returns the trigonometric cotangent of the specified angle, in radians, in the specified float expression.

Syntax:-

COT ( float_expression )

Here 'float_expression' is an expressionof type float or of a type that can be implicitly converted to float. This function returns a float value.

Example:-

The following example returns the COT for the specific angle.

DECLARE @angle float;
SET @angle = 124.1332;
SELECT 'The COT of the angle is: ' + CONVERT(varchar,COT(@angle));
GO
--The COT of the angle is: -0.040312 

DEGREES

Returns the corresponding angle in degrees for an angle specified in radians,

Syntax:-

DEGREES ( numeric_expression )

Here 'numeric_expression' is an expression of the exact numeric or approximate numeric data type category, except for the bit data type and this function returns the same type as numeric_expression.

Example:-

The following example returns the number of degrees in an angle of 5 radians.

SELECT 'The number of degrees in 5 radians is: ' + 
CONVERT(varchar, DEGREES(5));
GO
--The number of degrees in 5 radians is: 286 

EXP

Returns the exponential value of the specified float expression.

Syntax:-

EXP ( float_expression )

Here 'float_expression' is an expression of type float or of a type that can be implicitly converted to float. This function returns a float value.

Example:-

The following example declares a variable and returns the exponential value of the specified variable (10) with a text description.

DECLARE @var float
SET @var = 10
SELECT 'The EXP of the variable is: ' + CONVERT(varchar,EXP(@var))
GO
--The EXP of the variable is: 22026.5 

FLOOR

Returns the largest integer less than or equal to the specified numeric expression.

Syntax:-

FLOOR ( numeric_expression )

Here 'numeric_expression' is an expression of the exact numeric or approximate numeric data type category, except for the bit data type. Function returns the same type as numeric_expression.

Example:-

The following example shows positive numeric, negative numeric, and currency values with the FLOOR function.

SELECT FLOOR(123.45), FLOOR(-123.45), FLOOR($123.45);
--123    -124    123.00 

LOG

Returns the natural logarithm of the specified float expression in SQL Server.

Syntax:-

LOG ( float_expression [, base ] ) 

Here 'float_expression' is an expression of type float or of a type that can be implicitly converted to float, 'base' is an optional integer argument that sets the base for the logarithm. This function returns a float value.

Note: Optional parameter 'base' applies to SQL Server 2012 through SQL Server 2014. By default, LOG() returns the natural logarithm. Starting with SQL Server 2012, you can change the base of the logarithm to another value by using the optional base parameter.

Example:-

The following example calculates the LOG for the specified float expression.

DECLARE @var float = 10;
SELECT 'The LOG of the variable is: ' + CONVERT(varchar, LOG(@var));
GO
--The LOG of the variable is: 2.30259 

LOG10

Returns the base-10 logarithm of the specified float expression.

Syntax:-

LOG10 ( float_expression )

Here 'float_expression' is an expression of type float or of a type that can be implicitly converted to float. This function returns a float value.

Example:-

The following example calculates the LOG10 of the specified variable.

DECLARE @var float;
SET @var = 145.175643;
SELECT 'The LOG10 of the variable is: ' + CONVERT(varchar,LOG10(@var));
GO
--The LOG10 of the variable is: 2.16189 

PI

Returns the constant value of PI.

Syntax:-

PI ( )  

This function returns a float value.

Example:-

SELECT PI();
GO
--3.14159265358979 

POWER

Returns the value of the specified expression to the specified power.

Syntax:-

POWER ( float_expression , y )

Here 'float_expression' is an expression of type float or of a type that can be implicitly converted to float. 'y' is the power to which to raise float_expression. y can be an expression of the exact numeric or approximate numeric data type category, except for the bit data type. This function returns the same type as submitted in float_expression

Example:-

DECLARE @input1 float;
SET @input1= 2;
SELECT POWER(@input1, 3) AS Result1
--8 

RADIANS

Returns radians when a numeric expression, in degrees, is entered.

Syntax:-

RADIANS ( numeric_expression ) 

Here 'numeric_expression' is an expression of the exact numeric or approximate numeric data type category, except for the bit data type. This function returns the same type as numeric_expression.

Example:-

DECLARE @angle float
SET @angle = -45.01
SELECT 'The RADIANS of the angle is: ' +
   CONVERT(varchar, RADIANS(@angle))
GO
--The RADIANS of the angle is: -0.785573 

RAND

Returns a pseudo-random float value from 0 through 1, exclusive.

Syntax:-

RAND ( [ seed ] )

Here 'seed' is an integer expression (tinyint, smallint, or int) that gives the seed value.

Note: Repetitive calls of RAND() with the same seed value return the same results. If seed is not specified, the SQL Server Database Engine assigns a seed value at random. For a specified seed value, the result returned is always the same.

Example:-

SELECT RAND(100), RAND(), RAND() 
--0.715436657367485        0.28463380767982    0.0131039082850364

The above query will always return the same sequence of numbers. This is because, For one connection, if RAND() is called with a specified seed value, all subsequent calls of RAND() produce results based on the seeded RAND() call.

SELECT RAND() 
-- 0.338586781089383

The above query will return a new random number every time you execute.

ROUND

Returns a numeric value, rounded to the specified length or precision.

Syntax:-

ROUND ( numeric_expression , length [ ,function ] )

Here 'numeric_expression' is an expression of the exact numeric or approximate numeric data type category, except for the bit data type.

'length' is the precision to which numeric_expression is to be rounded. length must be an expression of type tinyint, smallint, or int. When length is a positive number, numeric_expression is rounded to the number of decimal positions specified by length. When length is a negative number, numeric_expression is rounded on the left side of the decimal point, as specified by length.

'function' is the type of operation to perform. function must be tinyint, smallint, or int. When function is omitted or has a value of 0 (default), numeric_expression is rounded. When a value other than 0 is specified, numeric_expression is truncated.

ROUND always returns a value. If length is negative and larger than the number of digits before the decimal point, ROUND returns 0. The function return types are listed below as per the Expression,

tinyint - int

smallint - int

int - int

bigint - bigint

decimal and numeric category (p, s) - decimal(p, s)

money and smallmoney category - money

float and real category - float

Example:-

The following example shows two expressions that demonstrate by using ROUND the last digit is always an estimate.

SELECT ROUND(123.9994, 3), ROUND(123.9995, 3);
GO
--123.9990        124.0000 

SIGN

Returns the positive (+1), zero (0), or negative (-1) sign of the specified expression.

Syntax:-

SIGN ( numeric_expression )

Here 'numeric_expression' is an expression of the exact numeric or approximate numeric data type category, except for the bit data type. The function return type as per the specified expression is given below,

bigint - bigint

int/smallint/tinyint - int

money/smallmoney - money

numeric/decimal - numeric/decimal

Other types - float

Example:-

The following example returns the SIGN values of numbers different numbers,

SELECT SIGN(-5), SIGN(0), SIGN(7) , SIGN(-2)
--    -1    0    1    -1 

SIN

Returns the trigonometric sine of the specified angle, in radians, and in an approximate numeric, float, expression.

Syntax:-

SIN ( float_expression )

Here 'float_expression' is an expression of type float or of a type that can be implicitly converted to float. This function returns a float value.

Example:-

The following example calculates the SIN for a specified angle.

DECLARE @angle float;
SET @angle = 45.175643;
SELECT 'The SIN of the angle is: ' + CONVERT(varchar,SIN(@angle));
GO
--The SIN of the angle is: 0.929607 

SQRT

Returns the square root of the specified float value.

Syntax:-

SQRT ( float_expression )

Here 'float_expression' is an expression of type float or of a type that can be implicitly converted to float. This function returns a float value.

Example:-

The following example returns the square root of number 25,

DECLARE @myvalue float;
SET @myvalue = 25.00;
SELECT SQRT(@myvalue);
--5 

SQUARE

Returns the square of the specified float value.

Syntax:-

SQUARE ( float_expression ) 

Here 'float_expression' is an expression of type float or of a type that can be implicitly converted to float. This function returns a float value.

Example:-

The following example returns square of number 5,

DECLARE @myvalue float;
SET @myvalue = 5.00;
SELECT SQUARE(@myvalue);
--25 

TAN

Returns the tangent of the input expression.

Syntax:-

TAN ( float_expression )  

Here 'float_expression' is an expression of type float or of a type that can be implicitly converted to float. This function returns a float value.

Example:-

The following example returns the tangent of PI()/2.

SELECT TAN(PI()/2);
--1.63312393531954E+16 

References

The main references are given below,

Summary

In this article I have explained Mathematical Functions of SQL Server (2000 and above) from a beginner's perspective. If I have missed anything or need any change in definition then please let me know. I hope you have enjoyed this article and got some value addition to your knowledge.

You might be interested in the below articles.

I have put my time and efforts on all of my articles, Please don't forget to mark your votes, suggestions and feedback to improve the quality of this and upcoming articles. Thanks for reading.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Shemeer NS
Software Developer (Senior)
India India
Technology Specialist | CodeProject MVP | Visual Studio Gallery Contributor | Author | Geek | Netizen | Husband | ChessPlayer
 
Most of my articles are listed on top 5 of the respective 'Best articles of the month' and some of my articles are published on ASP.NET WebSite's Article of the Day section.
 
Check my contributions in Visual Studio Gallery and Code Project
 
Technical Blog: http://www.shemeerns.com
Facebook: http://facebook.com/shemeernsblog
Twitter : http://twitter.com/shemeerns
Google+ : http://google.com/+Shemeernsblog
Follow on   Twitter   Google+

Comments and Discussions

 
SuggestionSpeed test results PinmemberGreg Rairdon1-Jun-14 9:56 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.1411019.1 | Last Updated 14 Jun 2014
Article Copyright 2014 by Shemeer NS
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid