Sql Server material 9

FUNCTIONS: SQL Server 2005 provides built-in functions that can be used to perform certain operations. Functions can be used or included in the following:

-          The select list of a query that uses a SELECT statement to return a value.

-          A WHERE clauses search condition of a SELECT statement to limit the rows that qualify for the query.

Syntax for executing a function:

SELECT <Fun Name> ( [ <expressions> ] )

-The expression can be a constant values or a name of a column.

Functions can be classified into 2 types:

            -Single Row Functions

            -Group Functions

A single row function executes once for each row that is present in the table where as group functions take multiple rows into consideration and returns a single value as output.

Single Row Function Categories:

            -Mathematical Functions

            -String Functions

            -Date and Time Functions

            -System Functions

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

<<<Previous                                                                                                                              Next >>>