Introduction

There has been several improvement in SQL Server 2012. Below are some favorite features and practical example of the new features.



1.  IIF()

IIF() is used to short circuit evaluation similar to the same function in visual basic and reporting service.
IIF() is now available as a new T-SQL keyword.

    Declare @x int
    select @x=1
    select iif(@x=1,'true', 'false') as xisone
    
    --------result ----
    xisone
    true


See iif() on technet for more example.

2.  CONCAT()

Concat is used for concatenating colums and string. Its a powerful function that you need to try.
Prior to concat we used several function like coallesce to deal with null. The statement bellow
will result to null.

select 'hello' + 'world' + null
------------------
result
null

But with concat.

select Concat('hello ' , 'world' , null) as result
-----------------
Results
hello world

other example to try:

select Concat('hello ' , 'world ' , 2014) as result
--------------
result
hello world 2014

For more information on Concat() please refere to this link

3.  Try_convert()

Try_convert function will do the conversion if it can convert to the target data type. If it can't
it will return null. 


    select
     try_convert(int,'this will not convert') as invalidint,
     try_convert(int,2000.00) as validint,
     try_convert(bit, 'true') as bitsample,
     try_convert(date, 'january 1, 2012') as datesample,
     try_convert(date, 'feb 29, 2012') as datesample
    
    result
    invalidint validint bitsample datesample datesample
    NULL     2000            1            2012-01-01    2012-02-29

for more example please visit this link

4.  Try_cast()

Almost similar functionality to try_convert(). It will convert the value to the target datatype 
otherwise it will return null without any error.

 select
     try_convert(int,'this will not convert') as invalidint,
     try_convert(int,2000.00) as validint,
     try_convert(bit, 'true') as bitsample,
     try_convert(date, 'january 1, 2012') as datesample,
     try_convert(date, 'feb 29, 2012') as datesample

  result
    
invalidint validint bitsample datesample datesample
    NULL     2000            1            2012-01-01    2012-02-29


For more information on try_cast() please refer to this technet link.


5.  Format()

Format() is a tsql keyword similar to the one used in the .net programming world
Format is a powerfull keyword but it will have to rely on the CLR framework in SQL server

DECLARE @d DATETIME = '10/01/2011';
SELECT FORMAT ( @d, 'd', 'en-US' ) AS 'US English Result'
      ,FORMAT ( @d, 'd', 'en-gb' ) AS 'Great Britain English Result'
      ,FORMAT ( @d, 'd', 'de-de' ) AS 'German Result'
      ,FORMAT ( @d, 'd', 'zh-cn' ) AS 'Simplified Chinese (PRC) Result';

US English Result Great Britain English Result  German Result Simplified Chinese (PRC) Result
----------------  ----------------------------- ------------- -------------------------------------
10/1/2011         01/10/2011                    01.10.2011    2011/10/1

For more information on the format keyowrd please refer to this link

6.  EOMonth()


End of the month function returns the end of the month date for a specified  date.

DECLARE @date DATETIME = GETDATE();
SELECT EOMONTH ( @date ) AS 'This Month';
SELECT EOMONTH ( @date, 1 ) AS 'Next Month';
SELECT EOMONTH ( @date, -1 ) AS 'Last Month';
GO
results

This Month
-----------------------
2011-12-31
(1 row(s) affected)
Next Month
-----------------------
2012-01-31
(1 row(s) affected)
Last Month
-----------------------
2011-11-30
(1 row(s) affected)
For more info on EOMONTH() please see this link.

7. Choose()

Choose Returns the item at the specified index from a list of values in SQL Server 2012.


    SELECT CHOOSE ( 3, 'Manager', 'Director', 'Developer', 'Tester' ) AS Result;

    Result
    -------------
    Developer

How to make use of this function
and here's a quick introduction

8. Exec()


There has been some enhancements in the execute key word.
It can now specify the metadata returned from the statement by using the
WITH RESULT SETS argument

     exec ('select 2 as a,5 as b')
     WITH RESULT SETS
     (
     (a int,
     b money)
     )


results
     a     b
    ---  ----
    2      5.00

For more information on exec please see this link