SQL SERVER 2008 – FUNCTIONS In SQL SERVER 2008 – Mathematical Functions

These functions perform a calculation, usually based on input values that are provided as arguments, and return a numeric value; they take “n” as input where n is a numeric expression.

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

Select ABS(10)                                   Ouput: 10

Select ABS(-10)                                  Ouput: 10

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

SELECT CEILING(15.6)                  OUTPUT: 16

SELECT CEILING(15.6)                  OUTPUT: -15

CEILING (n): Returns the largest integer less than or equal to the specified numeric expression.

SELECT FLOOR(15.6)                                 OUTPUT: 15

SELECT FLOOR(15.6)                                 OUTPUT: -16

LOG (n): Returns the natural logarithm of the specified expression, i.e. base-e

SELECT LOG(10)                             OUTPUT: 2.30258509299405

LOG10 (n): Returns base-10 logarithm of the specified expression, i.e. base e

SELECT LOG10(10)                         OUTPUT: 1

PI(): Returns the constant value of PI.

SELECT PI()                                      OUTPUT: 3.14159265358979

POWER(n, m): Returns the value of the specified expression n to the specified power m.

SELECT POWER(10, 3)                                OUTPUT: 1000

RAND ( [SEED] ): Returns a random float value from 0 through 1.

–          SEED: Is an integer expression that gives the seed value. If seed is not specified, the Database Engine assigns a seed value at random. For a specified seed value, the result returned is always the same.

SELECT RAND()      -Each time we execute we get a random value.

SELECT RAND(100)            -Each time we execute we get the same value.

ROUND ( n , length [ ,function ] ): Returns a numeric expression, rounded to the specified length or precision.

SELECT ROUND(156.567, 2)                      OUTPUT: 156.57

SELECT ROUND(156.567, 1)                      OUTPUT: 156.6

SELECT ROUND(156.567, 0)                      OUTPUT: 157

-If the seed is positive rounding will be done after the decimal, if it is negative rounding will be done before the decimal:

SELECT ROUND(156.567, -1)                     OUTPUT: 160

SELECT ROUND(156.567, -2)                     OUTPUT: 200

-If we specify the optional parameter function that is an integer value we can decide to truncate the value or round the value. If it is 0 (default) rounds the value and value greater than 0 truncates the value.

SELECT ROUND(156.567, 2, 1)                  OUTPUT: 156.56

SELECT ROUND(156.567, -2, 1)                 OUTPUT: 100

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

–          If n<0 it returns -1

–          If n=0 it returns 0

–          If n>0 it returns 1

SELECT SIGN(-100)                                     OUTPUT: -1

SELECT SIGN(0)                                          OUTPUT: 0

SELECT SIGN(100)                                      OUTPUT: 1

SQRT(n): Returns the square root of the specified expression.

SELECT SQRT(81)                                        OUTPUT: 9

SELECT SQRT(30)                                        OUTPUT: 5.47722557505166

SQUARE(n): Returns the square of the specified expression.

SELECT SQUARE(35)                                 OUTPUT: 1225

-Apart from the above it provides with trigonometric function like COS, COT, SIN, TAN, ACOS, ASIN, ATAN for which we need to provide the degrees.

Reference : Dilip Kumar Jena ( https://sqlexplore.wordpress.com )


2 thoughts on “SQL SERVER 2008 – FUNCTIONS In SQL SERVER 2008 – Mathematical Functions

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s