SQL Server 2012 Rewind
I’ve been reviewing some of the new features released in the SQL Server 2012 release. I thought I would remind myself from where we have come as the CTPs continue to progress for SQL Server 2014. There are a few more features than I remembered. After a while it all merges together.
Two of my favorite enhancements are functions that make writing queries easier. There are other techniques to substitute for each of them. However, these functions make the code more readable and perform nicely.
IIF is an inline if statement allowing you to specify a value to return if the test condition is met, and an alternate value if the test condition fails. This can be accomplished with a CASE operation. But, the IIF function is more specific for a pass/fail scenario, and is familiar to programmers of many other languages.
CHOOSE is the second function I really like a lot. It allows you to specify an array of values, and to select one of the set based on its ordinal position. You can do the same thing using an CTE, a lookup table or even SUBSTRING.
For example, to select the 3 character month based on the number of the month you could use the SUBSTRING function as follows:
SUBSTRING(‘JANFEBMARAPRMAYJUNNULAUGSEPAUGNOVDEC’, (MonthNumber * 3) -2)
Now with choose you can use something more readable:
CHOOSE(MonthNumber, ‘JAN’, ‘FEB’,’MAR’, ‘APR’, ‘MAY’, ‘JUN’, ‘JUL’, ‘AUG’, ‘SEP’, ‘OCT’, ‘NOV’, ‘DEC’)
The CHOOSE method is not that different. However, the intention of the code is more clear, and the length of each individual item does not have to be consistent.
Do you have favorite functions from any release in SQL Server? Share the function or syntax believe to bring the most value to the SQL Language. Leave your comment here online or drop me an Email at btaylor@sswug.org.
Cheers,
Ben