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.
- SQL Server 2012 - What's New when Writing SQL
- Storing Files in SQL Server 2012
- What's New in SQL 2012 - New Functions (this blog)
- 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 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
SELECT
FilmName,
FilmReleaseDate,
FORMAT(FilmReleaseDate,'dddd dd MMMM yyyy')
AS FormattedDate
FROM
tblFilm
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
SELECT
FilmName,
-- assess film length using IIF
IIF(FilmRunTimeMinutes>150,'Long','Short')
AS FilmLengthUsingIIf,
-- assess film length using CASE WHEN
CASE
WHEN FilmRunTimeMinutes>150 THEN 'Long'
ELSE
'Short'
END as FilmLengthUsingCase
FROM
tblFilm
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:
SELECT
FilmName,
-- show number of Oscars as text
CHOOSE(FilmOscarWins+1,'Zero','One',
'Two','Three','Four','Five',
'Six','Seven','Eight',
'Nine','Ten','Eleven')
AS Oscars
FROM
tblFilm
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.
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:
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:
SELECT
PersonName,
CASE WHEN TRY_CONVERT(int,LuckyNumber) IS Null
THEN 0
ELSE
TRY_CONVERT(int,LuckyNumber)
END AS LuckyInteger
FROM
tblPerson
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.
String Concatenation
The CONCAT function provides an alternative way to concatenate two bits of text together:
SELECT
-- 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
FROM
tblFilm
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:
SELECT
-- 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.
- SQL Server 2012 - What's New when Writing SQL
- Storing Files in SQL Server 2012
- What's New in SQL 2012 - New Functions (this blog)
- Sequences and Page Data using OFFSET / FETCH NEXT