SQL SERVER 2008 – FUNCTIONS In SQL SERVER 2008 – SYSTEM FUNCTIONS

ISNUMERIC( expression ): Determines whether an expression is a valid numeric type. If it is numeric it returns 1 else return 0.

SELECT ISNUMERIC(100)                                     OUTPUT: 1

SELECT ISNUMERIC(‘100’)                                   OUTPUT: 1

SELECT ISNUMERIC(‘100A’)                                OUTPUT: 0

ISDATE (expression): Determines whether an input expression is a valid date or not. If it is a valid date it returns 1 else return 0. Valid date in the sense the expression, which is present in mm/dd/yy format.

SELECT ISDATE(’12/21/98′)                                    OUTPUT: 1

SELECT ISDATE(’21/12/98′)                                    OUTPUT: 0

ISNULL (expression1, expression2): if expression1 is null then it returns expression2.

SELECT ISNULL(100, 200)                                     OUTPUT: 100

SELECT ISNULL(NULL, 200)                                OUTPUT: 200

SELECT EMPNO, ENAME, SAL, COMM, SAL + COMM AS [TOTAL SAL] FROM EMP

-In above case if any of the value in the comm. Is null it returns null in the Total Sal because any arithmetic operations performed on a null value results to null only at this time the statement has to be written as following:

SELECT EMPNO, ENAME, SAL, COMM, SAL + ISNULL(COMM, 0) AS [TOTAL SAL] FROM EMP

COALESCE (expression1, expression2, …… expression n):  Returns the first not null expression in the list of expressions given, similar to isnull but we can give multiple values here.

SELECT COALESCE(NULL, 100, NULL, 200)     OUTPUT: 100

SELECT EMPNO, ENAME, SAL, COMM, SAL + COALESCE(COMM, 0) AS [TOTAL SAL] FROM EMP

DATALENGTH (expression) : Returns the number of bytes used to represent any expression.

SELECT DATALENGTH(100)                                OUTPUT: 4

SELECT DATALENGTH(‘HELLO’)                      OUTPUT: 5

HOST_NAME(): Returns the name of the workstation.

SELECT HOST_NAME()

IDENT_CURRENT(‘table_name’): Returns the last identity value generated for a specified table by the identity function.

SELECT IDENT_CURRENT(‘BANK’)

IDENT_SEED(‘table_name’): Returns the seed value that was specified when the identity function in a table was created.

SELECT IDENT_SEED(‘BANK’)

IDENT_INCR(‘table_name’): Returns the increment value that was specified when the identity function in a table was created.

SELECT IDENT_INCR(‘BANK’)

NEWID( ): Creates a unique value of type uniqueidentifier.

SELECT NEWID()

NULLIF(expression1, expression2): Returns the first expression if the two expressions are not equivalent. If the expressions are equivalent, returns a null value.

SELECT NULLIF(100, 200)                                     OUTPUT: 100

SELECT NULLIF(100, 100)                                     OUTPUT: NULL

ROWCOUNT_BIG(): Returns the number of rows affected by the last statement executed. If we use this after a select statement it will return us the number of rows the select statement has returned.

SELECT * FROM EMP

SELECT ROWCOUNT_BIG FROM EMP

APP_NAME(): Returns the name of the application from where the statement is executed.

SELECT APP_NAME()

CASE: Evaluates a list of conditions and returns one of multiple possible result expressions. It has two formats:

-The simple CASE function compares an expression to a set of simple expressions to determine the result.

-The searched CASE function evaluates a set of Boolean expressions to determine the result.

– Both formats support an optional ELSE argument.

 

CASE <expression>

WHEN when_expression THEN result_expression

WHEN when_expression THEN result_expression

…………………………

ELSE else_result_expression

END

 

-In this case if the expression matches with any of the when_expression it returns the corresponding result_expression, if it does not match with any then it returns else_result_exression.

SELECT EMPNO, ENAME, SAL, JOB,

(CASE JOB

WHEN ‘PRESIDENT’ THEN ‘BIG BOSS’

WHEN ‘MANAGER’ THEN ‘BOSS’

WHEN ‘ANALYST’ THEN ‘SCIENTIST’

ELSE ‘EMPLOYEE’

END) AS COMMENTS FROM EMP

 

SELECT EMPNO, ENAME, JOB, SAL,

(CASE SIGN(SAL-3000)

WHEN 1 THEN ‘ABOVE TARGET’

WHEN 0 THEN ‘ON TARGET’

WHEN –1 THEN ‘BELOW TARGET’

END) AS COMMENTS FROM EMP

 

-The above statement can be written in one more way also by using the second format of the CASE function.

 

CASE

WHEN condition THEN result_expression

WHEN condition THEN result_expression

…………………………

ELSE else_result_expression

END

 

SELECT EMPNO, ENAME, JOB, SAL,

(CASE

WHEN SAL>3000 THEN ‘ABOVE TARGET’

WHEN SAL=3000 THEN ‘ON TARGET’

WHEN SAL<3000 THEN ‘BELOW TARGET’

END) AS COMMENTS FROM EMP

 

Set Operators:

 

COUNT(expression): Returns the number of items in a group.

SELECT COUNT(*) FROM EMP

SELECT COUNT(*) FROM EMP WHERE DEPTNO=20

SELECT COUNT(COMM) FROM EMP

 

COUNT_BIG(expression): COUNT_BIG works like the COUNT function. The only difference between the two functions is their return values. COUNT_BIG always returns a bigint data type value. COUNT always returns an int data type value.

SELECT COUNT_BIG(*) FROM EMP

 

SUM(expression): Returns the sum of all the values. SUM can be used with numeric columns only. Null values are ignored

SELECT SUM(SAL) FROM EMP

 

AVG(expression): Returns the average of the values in a group. Null values are ignored.

SELECT AVG(SAL) FROM EMP

 

MAX(expression): Returns the maximum value in the expression.

SELECT MAX(SAL) FROM EMP

 

MIN(expression): Returns the minimum value in the expression.

SELECT MIN(SAL) FROM EMP

 

STDEV(expression): Returns the statistical standard deviation of all values in the specified expression.

SELECT STDEV(SAL) FROM EMP

 

VAR(expression): Returns the statistical variance of all values in the specified expression.

SELECT VAR(SAL) FROM EMP

 

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

Advertisements

2 thoughts on “SQL SERVER 2008 – FUNCTIONS In SQL SERVER 2008 – SYSTEM FUNCTIONS

  1. This is one of the best answer so far, I have read online. Just useful information. Very well presented. I had found another good collection of php interview question and answer over internet.
    please check out this link…

    http://mindstick.com/Articles/9b3c9e65-6152-4933-acf8-15f8bbd3ff77/?Function%20in%20SQL

    Thanks

  2. well done mate, good site this information was compact but very usefull. like it should by 😛 in every lifesituation, also this helped me out with schoolwork. en keep up the good work !!

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