New features in SQL Server 2012

CHOOSE () : Returns the item at the specified index from the list of values

Syntax: CHOOSE ( index, val_1, val_2 [, val_n ] )

Example:

SELECT DATENAME(MM,GETDATE()) MONTH_NM,CHOOSE(DATEPART(Q,GETDATE()),'1st Qtr','2nd Qtr','3rd Qtr','4th Qtr') QTR

Output:

MONTH_NM                       QTR

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

February                       1st Qtr

SELECT DATENAME(MM,DATEADD(M,2,GETDATE())) MONTH_NM,CHOOSE(DATEPART(Q,DATEADD(M,2,GETDATE())),'1st Qtr','2nd Qtr','3rd Qtr','4th Qtr') QTR

Output:

MONTH_NM                       QTR

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

April                          2nd Qtr

SELECT DATENAME(MM,DATEADD(M,5,GETDATE())) MONTH_NM,CHOOSE(DATEPART(Q,DATEADD(M,5,GETDATE())),'1st Qtr','2nd Qtr','3rd Qtr','4th Qtr') QTR

Output:

MONTH_NM                       QTR

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

July                           3rd Qtr

SELECT DATENAME(MM,DATEADD(M,8,GETDATE())) MONTH_NM,CHOOSE(DATEPART(Q,DATEADD(M,8,GETDATE())),'1st Qtr','2nd Qtr','3rd Qtr','4th Qtr') QTR

Output:

MONTH_NM                       QTR

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

October                        4th Qtr

       Note: What happens if the index is out of range of specified list of values? Try this:

SELECT CHOOSE(5,'First','Second') as OUT_OF_RANGE

Output:

OUT_OF_RANGE

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

NULL

 It returns NULL

<<IIF()                                                    Go to Main Page                           

New features in SQL Server 2012

IIF() : Returns one of two values depending on condition is True or False

Syntax:IIF (condition, if true, if false).

It can be used instead of CASE when we want to check a condition and based on the output (True/False) pick one value/column.

Eg: SELECT IIF (0 > 1,'TRUE','FALSE') AS CASE_REPLACE;

Output:

CASE_REPLACE

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

FALSE

If we use CASE then above scenario would be like this:

SELECT (CASE WHEN 0>1 THEN 'TRUE' ELSE 'FALSE' END) AS [CASE_OUPUT]

Output:

CASE_OUPUT

----------

FALSE

IIF looks comparatively simple, but CASE has it’s on edge over IIF as it can be used to evaluate more condition by using ‘WHEN-THEN’

<<EOMONTH()                                Go to Main Page                                CHOOSE ()>>

New features in SQL Server 2012

EOMONTH(): Returns the last day for a specified month

Syntax: EOMONTH ( start_date [, month_to_add ] )

start_date:Date expression specifying the date for which to return the last day of the month.

month_to_add (Optional):integer expression specifying the number of months to add to start_date.

SELECT EOMONTH (GETDATE()) AS 'Last day of This Month';

--Output:

Last day of This Month

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

2013-02-28

SELECT EOMONTH (GETDATE(),1 ) AS 'Last day of Next Month';

--Output:

Last day of Next Month

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

2013-03-31

SELECT EOMONTH (GETDATE(),-1 ) AS 'Last day of Last Month';

--Output:

Last day of Last Month

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

2013-01-31

<<LAG()                                                           Go To Main Page

New features in SQL Server 2012

LAG() Function to fetch Previous Value

Syntax: LAG (col [, offset], [default])  OVER ([partition by col] order by col)

Offset argument determines the number of previous rows in order that the SQL Engine will read from. Offset input parameter is optional. If nothing is provided, then the default value 1 will be used in LAG() function

The default argument sets the value which will be returned if SQL Lag() function returns nothing.

CREATE TABLE #EMP_SALARY (ID INT,EMP_ID INT,SAL_MONTH VARCHAR(10),SALARY INT)

INSERT INTO #EMP_SALARY VALUES (1,1,'Jan-13',5000),(2,2,'Jan-13',6000),(3,1,'Feb-13',6000),(4,2,'Feb-13',4000)

SELECT * FROM #EMP_SALARY ORDER BY EMP_ID,ID

ID EMP_ID SAL_MONTH  SALARY

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

1   1      Jan-13     5000

3   1      Feb-13     6000

2   2      Jan-13     6000

4   2      Feb-13     4000

SELECT ID,EMP_ID,SAL_MONTH,SALARY,LAG(SALARY,1,NULL) OVER(PARTITION BY EMP_ID ORDER BY ID) AS PREV_SAL

FROM #EMP_SALARY

--Output:

ID EMP_ID SAL_MONTH  SALARY PREV_SAL

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

1   1      Jan-13     5000  NULL

3   1      Feb-13     6000  5000

2   2      Jan-13     6000  NULL

4   2      Feb-13     4000  6000

<< Exception handling                                  Go to Main Page                                           EOMONTH()>>