New T-SQL feature in SQL Server 2012

아티클
1/17/2024

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

다음을 통해 공유