BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
SQL 2012 has 14 new functions, a new FileTable construct and new ways to sequence and fetch records - as this blog explains.
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 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:
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')
Here's what the SQL above would typically show:
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
-- 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:
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
This would produce the following output:
If a film wins zero Oscars, for example, then adding 1 to this gives the first item in the list: Zero.
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:
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:
Here Colin's lucky number failed to convert to an integer, so we display this as 0.
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:
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:
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.