Mathamatical Functions in SQL Server

Mathamatical Functions:

• Abs
• Floor
• Round
• Sqrt
• Rank

Abs Functions:

A mathematical function that returns the absolute (positive) value of the specified numeric expression.

Syntax : ABS ( numeric_expression )

Example : SELECT ABS(-1.0), ABS(0.0), ABS(1.0);

o/p : ---- ---- ----

         1.0  .0   1.0

FLOOR :

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

Syntax : FLOOR ( numeric_expression )

Example : 

                   SELECT FLOOR(123.45), FLOOR(-123.45), FLOOR($123.45)

O/P            ---------      ---------     -----------

                   123            -124          123.0000   

Round :

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

Syntax:

ROUND ( numeric_expression , length [ ,function ] )

Example:

SELECT ROUND(123.4545, 2);

GO

SELECT ROUND(123.45, -2);

GO

Here is the result set.

----------

123.4500

(1 row(s) affected)

--------

100.00

(1 row(s) affected)

SQRT: Returns the square root of the specified float value.

Syntax:

SQRT ( float_expression )

Example

DECLARE @myvalue float;

SET @myvalue = 1.00;

WHILE @myvalue < 10.00

   BEGIN

      SELECT SQRT(@myvalue);

      SET @myvalue = @myvalue + 1

   END;

GO

Here is the result set.

------------------------ 

1.0                      

------------------------ 

1.4142135623731          

------------------------ 

1.73205080756888         

------------------------ 

2.0                      

------------------------ 

2.23606797749979         

------------------------ 

2.44948974278318         

------------------------ 

2.64575131106459         

------------------------ 

2.82842712474619         

------------------------ 

3.0

CEILING: Returns the smallest integer greater than, or equal to, the specified numeric expression.

Syntax :

CEILING ( numeric_expression )

Example :

SELECT CEILING($123.45), CEILING($-123.45), CEILING($0.0)

Result:

--------- --------- ------------------------- 

124.00    -123.00    0.00