SQL Server math functions help us round numbers, calculate powers and work with angles. I keep a short list handy because each function answers a different question.
Common Math Functions
Here are functions I reach for when a query needs a calculation. Test the input range and data type before using them with production data.
- ABS: returns the absolute value.
- CEILING: returns the smallest integer greater than or equal to the input.
- FLOOR: returns the largest integer less than or equal to the input.
- ROUND: rounds a value to the requested precision.
- POWER and SQRT: calculate a power and a square root.
- EXP, LOG and LOG10: calculate an exponential value and logarithms.
- SIGN: reports whether a number is positive, negative or zero.
- RAND: returns a pseudorandom float value.
The remainder operator is %. It is an operator rather than a function, but it belongs in the same toolbox.
Try a Few Examples
Negative numbers make the difference between CEILING and FLOOR easy to see. One moves to negative one, and the other moves to negative two.
SELECT ABS(-12) AS AbsoluteValue,
CEILING(-1.2) AS CeilingValue,
FLOOR(-1.2) AS FloorValue;
SELECT ROUND(CAST(-1.25 AS decimal(4,2)), 1) AS RoundedValue,
17 % 5 AS Remainder;The first query returns 12, -1 and -2. The second returns -1.30 and 2. The explicit decimal type makes the rounding example easier to read.
Trigonometry and Other Calculations
SQL Server also has SIN, COS, TAN and COT, plus ASIN, ACOS, ATAN and ATN2. COT returns the cotangent, not the hyperbolic cosine. Trigonometric function inputs use radians where applicable.
SELECT CAST(SQRT(81) AS decimal(5,2)) AS SquareRoot,
CAST(COT(PI()/4) AS decimal(5,2)) AS Cotangent;This example returns 9.00 and 1.00. A function can reject an input outside its domain, so validate values before calling it.
A Note About RAND and Aggregates
RAND returns a pseudorandom float from zero up to, but not including, one. Do not assume that calling RAND in a SELECT gives every row an independent random value. Use an approach suited to the result you need.
SELECT RAND() AS RandomValue;
Your result will be at least zero and less than one. ISNULL handles NULL values, while STDEV and VAR are aggregate functions. They are useful, but they do different jobs from the scalar math functions above.
Check the result type as well as the input. For example, ABS can overflow for the smallest value of a signed integer type. If your input can reach that boundary, convert it to a wider type before applying ABS.
Math functions are not interchangeable shortcuts, they are tools with specific inputs and results.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
8 Comments. Leave new
is there a way to do a division and modulo in the same statement? I’d like the result of the division to go into one variable and the remainder into another.
how to use MOD function in sql server 2005 .
I want record like 101 201 301 out of 100000 rows
How to get highest value from 5 local variables in a stored procedure.
Eg:
Declare @var1 as int, @var2 as int, @var3 as int, @var4 as int, @var5 as int
Set @var1=55
Set @var2=70
Set @var3=90
Set @var4=90
Set @var5=25
I need value distinct 90 as it is highest among all variables. I mentioned distinct because it repeats in @var3 and @var4 both.
Thanks
create table temp(col1 integer)
select * from temp
DECLARE @temp TABLE(col1 INTEGER)
DECLARE @r int=55
DECLARE @s int=70
DECLARE @t int=90
DECLARE @q int=70
DECLARE @x int=90
INSERT INTO temp(col1) values(@r)
INSERT INTO temp(col1) values(@s)
INSERT INTO temp(col1) values(@t)
INSERT INTO temp(col1) values(@q)
INSERT INTO temp(col1) values(@x)
SELECT MAX(col1) FROM temp
How to get a quotient of two numbers ? what is the function used to get it ?
SQUARE() Float expression
Square of the given numeric expression. Equivalent of POWER function when raised to 2nd power. Example:
SELECT SQUARE(3)
9
SELECT SQUARE(9);
SELECT POWER(9,2);
It’s So Nice.
can you explain about “Convert number if NULL ISNULL”