WiseOwl Training - Established 1992 Wise Owl Training

Established May 1992
30 years in business
Wise Owl Training
30 years in business
See 479 reviews for our classroom and online training
New features for SQL within SQL Server 2012
Part three of a four-part series of blogs

SQL 2012 has 14 new functions, a new FileTable construct and new ways to sequence and fetch records - as this blog explains.

  1. SQL Server 2012 - What's New when Writing SQL
  2. Storing Files in SQL Server 2012
  3. What's New in SQL 2012 - New Functions (this blog)
  4. Sequences and Page Data using OFFSET / FETCH NEXT

Posted by Andy Brown on 08 May 2012

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

What's New in SQL 2012 - New Functions

SQL Server 2012 has introduced 14 new functions (and added an extra argument to the LOG function).  I've listed them, then given details and examples below.

Summary of the New SQL 2012 Functions

The 14 new functions are shown on Microsoft's site, and reproduced below:

The 14 new SQL functions

The new functions are shown on the left, with some examples given below.


The system functions available for any database don't seem to list the new functions - not sure if this is because I am running 2008 R2 and 2012 side by side:

List of system functions

You can expand the categories, but none of the 14 new functions appear to show up.


The FORMAT Function

SQL has been crying out for this for years!  Instead of using the messy CONVERT or CAST functions, you can use FORMAT instead:

-- show a list of films




FORMAT(FilmReleaseDate,'dddd dd MMMM yyyy')

AS FormattedDate



Here's what the SQL above would typically show:

The formatted date

The selected column shows the date with the required format.

Where has this function been for the last few years?  SQL will never be the same again!

The IIF and CHOOSE Functions

The IIF function is well-known in Microsoft Access and Reporting Services (to name but two), and also in Excel (where it is known as IF).  It allows you to write CASE statements more concisely. 

The following two SQL expressions are identical:

-- show a list of films



-- assess film length using IIF


AS FilmLengthUsingIIf,

-- assess film length using CASE WHEN


WHEN FilmRunTimeMinutes>150 THEN 'Long'



END as FilmLengthUsingCase



The two columns for the film length express it as Short if the film last less than 2 and a half hours, or Long otherwise:

Films by length

The first few films, with their lengths.

Personally I've grown used to using CASE WHEN (and like the fact that it can be easily extended to cover multiple scenarios), but some other programmers might welcome the introduction of IIF.

The CHOOSE function works in the same way as in Excel.  Here's an example, showing the number of Oscars won by films as words:



-- show number of Oscars as text





AS Oscars



This would produce the following output:

List of films and wordy oscars

If a film wins zero Oscars, for example, then adding 1 to this gives the first item in the list: Zero.


String Conversion

There are now several functions which try to parse data from one type to another, and return Null if the conversion fails.  Here's an example, using this table of data:

Table of persons

Each person has a lucky number entered - this should be as a number, but for Colin it isn't (it's text).


Here's the use of TRY_CONVERT to show the numbers as integers:



CASE WHEN TRY_CONVERT(int,LuckyNumber) IS Null




END AS LuckyInteger



This would return a table of data as follows:

Table of persons with lucky numbers

Here Colin's lucky number failed to convert to an integer, so we display this as 0.


String Concatenation

The CONCAT function provides an alternative way to concatenate two bits of text together:


-- concatenation using +

FilmName + ' (' + TRY_CONVERT(varchar(2), FilmOscarWins)

+ ' oscars)' AS FilmDescription1,

-- concatenation using new CONCAT function

CONCAT(FilmName,' (',TRY_CONVERT(varchar(2), FilmOscarWins),

' oscars)') AS FilmDescription2



Which of the above methods do you prefer?  They both give the same result:

Film descriptions

It's up to you which method you prefer - I think I'll be sticking with the + symbol to concatenate for now.


Date and Time Functions

I don't think there's much to get excited about there!  For the following example, you'll need to know that this blog was written on Monday 7th May 2012:


-- show date for given year, month and day

DATEFROMPARTS(2012,5,7) AS TodaysDate,

-- show the end of the month two months

-- before this one

EOMONTH(GetDate(),-2) AS TwoMonthsAgo

This SQL would give the following output:

Output from SQL

The SQL gives two dates as shown.


I'll be using the TRY_CONVERT function and FORMAT functions extensively, but I'm not sure I'll be using many others.


This blog has 0 threads Add post