New Built-In TSQL Functions in SQL Server 2012

Logical Functions

There are two new functions in this category : CHOOSE and IIF.

CHOOSE

CHOOSE can be used to return a value from a list of values based on an index number (note that index numbers start at 1). This function takes at least 2 arguments – the first argument must be an INT and the second argument and onwards can be any data type.

SELECT CHOOSE (1, 'A', 'B', 'C', 'D', 'E' ) AS [Using CHOOSE]
SELECT CHOOSE (4, 'A', 'B', 'C', 'D', 'E' ) AS [Using CHOOSE]
--Returns NULL if the index value exceeds the bounds of the array of values passed
SELECT CHOOSE (6, 'A', 'B', 'C', 'D', 'E' ) AS [Using CHOOSE]

IIF

If you have prior experience working with other programming languages you may have already encountered the IIF conditional function which is a compact way of writing IF..THEN..ELSE clause which returns the value based on the condition evaluation. The first argument of this function is a condition, if the condition is evaluated to TRUE then the second expression is evaluated and returned, if the condition is evaluated to FALSE then the third expression is evaluated and returned.

DECLARE @a int = 4;
DECLARE @b int = 40;
SELECT IIF ( @a > @b, 'Larger', 'Smaller' ) AS [Using CHOOSE]
SELECT IIF (1=1, 5+6, 1+2 ) AS [Using CHOOSE]
SELECT IIF (1=2, 5+6, 1+2 ) AS [Using CHOOSE]

String Functions

There are two new functions in this category – CONCAT and FORMAT.

CONCAT

You can pass two or more string values to the CONCAT function the function combines them all into string. All parameters passed to it of types other than a string will be converted to string implicitly and a NULL value will be converted to empty string (zero length string). The data type of the return value depends on the data type of the arguments passed.

SELECT CONCAT ('Today ', 'is ', '3rd', '-', 'January') AS [Using CONCAT]
SELECT CONCAT ('Today ', 'is ', 3, 'rd', '-', 'January') AS [Using CONCAT]

FORMAT

This function formats and returns the value passed to it in the specified format pattern. It returns a value of type nvarchar in case of success or NULL in case if the format pattern or culture is not valid. The performance of this function is slightly impaired as it relies on .NET CLR.

SELECT FORMAT(GETDATE(), 'dd/MM/yyyy' ) AS [Using FORMAT]
SELECT FORMAT(GETDATE(), 'dd-MMMM-yyyy') AS [Using FORMAT]
SELECT FORMAT(GETDATE(), 'dddd, MMMM dd, yyyy') AS [Using FORMAT]
SELECT FORMAT(GETDATE(), 'dd/MM/yyyy', 'en-US' ) AS [Using FORMAT]

Conclusion

In this article I talked about 14 new built in functions in four different categories coming up with SQL Server 2012 and which are going to make database developers lifes easier. These new useful functions emulate the functionality what we generally have in any other programming languages now at T-SQL/database engine level.

Resources

What’s New (Database Engine)

Programmability Enhancements (Database Engine)

Pages: 1 2




Related Articles :

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |